Hi There,
I'm having a similar problem/discussion here and found the following on the
Oracle technet site (http://technet.oracle.com). I have to try it out
myself, but maybe it helps. btw you need the Oracle JDBC to do this (see
import statement).
Joost
How do the JDBC drivers support Oracle REFCURSOR datatypes?
The Oracle JDBC driver supports bind variables of type REFCURSOR. A
REFCURSOR is represented by a JDBC ResultSet. Use the getCursor method of
the CallableStatement to convert a REFCURSOR value returned by a PL/SQL
block into a ResultSet. JDBC lets you call a stored procedure that executes
a query and returns a results set. Cast the corresponding CallableStatement
to oracle.jdbc.driver.OracleCallableStatement to use the getCursor method.
Importing classes from the oracle.jdbc.driver package makes programs more
readable. Here is a simple example. The samples subdirectory of the
distribution has additional examples.
import oracle.jdbc.driver.*;
...
CallableStatement cstmt;
ResultSet cursor;
// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
("begin open ? for select ename from emp; end;");
cstmt.registerOutParameter (1, OracleTypes.CURSOR);
cstmt.execute ();
cursor = ((OracleCallableStatement)cstmt).getCursor (1);
// Use the cursor like a normal ResultSet
while (cursor.next ())
{System.out.println (cursor.getString (1));}
> -----Original Message-----
> From: Conor D'Arcy [mailto:[EMAIL PROTECTED]]
> Sent: woensdag 12 juli 2000 8:30
> To: [EMAIL PROTECTED]
> Subject: Re: Resultset as outParameter...
>
>
> Hi again Mette,
> we're using Oracle8 release 2 and with those stored
> procedures our servlets
> cannot receive full resultSets (maybe Sybase can send Java
> resultSets?). We
> concluded that this problem lies more in the return types
> that JDBC can
> accept than servlets being unable to process a resultSet.
>
> So far, we're just getting VARCHARs from our stored
> procedures, and using
> them as return codes.
>
> If you run up against the same problem, one way of getting a
> resultSet for
> your servlet is to use your stored procedure to insert/update
> desired rows
> into a table. The stored procedure could return a code to
> your servlet if
> the insert was successful. Then in your servlet (if the return code
> signifies success) select from the table as per normal (i.e.
> a Statement or
> a PreparedStatement into a resultSet).
>
> However, if you can get a function to return a resultSet, I
> think most of us
> would be very interested in seeing a code sample! If you have further
> questions, as this is going away from servlets and more
> towards SQL & JDBC,
> it may be better to mail me offline (
> [EMAIL PROTECTED] ) and we can
> share our findings with the list when we have a conclusion.
>
> 'til later,
> Conor
>
> -----Original Message-----
> From: Muthukrishnan Giridhar [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, July 12, 2000 3:55 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Resultset as outParameter...
>
>
> you can probably use a Type called ARRAY which is supported
> in JDBC2.0.
> this should help u get multiple results
>
> Giri
>
>
>
>
>
>
>
> Mette Larsen <[EMAIL PROTECTED]> on 07/12/2000 04:05:49 PM
>
> Please respond to "A mailing list for discussion about Sun
> Microsystem's
> Java
> Servlet API Technology." <[EMAIL PROTECTED]>
>
>
>
>
>
>
>
>
> To: [EMAIL PROTECTED]
>
> cc: (bcc: Muthukrishnan Giridhar/E-Commerce/DSQ
> Software Limited/IN)
>
>
>
> Subject: Re: Resultset as outParameter...
>
>
>
>
>
>
>
>
> Hi Conor and others...
>
> Thanks for responding Conor!
> My "out parameter" is actually the result set (several rows).
> My stored
> procedure returns several rows, and I want to put this in a
> java.sql.ResultSet,
> so its not really an out-parameter...
> I read that a resultset also should be registered as an
> out-parameter, but
> how??
>
> So the problem is actually:
> How do I call a stored procedure which returns multiple rows??
> How do I retrieve the rows returned from the procedure??
>
>
> -----Original Message-----
> From: A mailing list for discussion about Sun Microsystem's
> Java Servlet
> API Technology. [mailto:[EMAIL PROTECTED]]On
> Behalf Of Conor
> D'Arcy
> Sent: Wednesday, July 12, 2000 11:11 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Resultset as outParameter...
>
>
> HI Mette,
> what goes in as the second parameter is the return type of
> the data that
> your stored procedure returns.
> or example, if you are returning a VARCHAR, your code will read
>
>
> cstmt.registerOutParameter(1,Types.VARCHAR);
> More info can be found at
> http://java.sun.com/j2se/1.3/docs/api/java/sql/CallableStatement.html
> By clicking on " registerOutParameter " method link you will
> get more info
> on your out parameter
> See also
> http://java.sun.com/j2se/1.3/docs/api/java/sql/Types.html " Types "
> where a list of valid types can be found.
>
> -----Original Message-----
> From: Mette Larsen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, July 12, 2000 8:53 AM
> To: [EMAIL PROTECTED]
> Subject: Resultset as outParameter...
>
>
> Hi All,
>
> I sure hope someone can help me with this problem, Ive been
> struggling with
> it for the past 3 days...
> I want to call i stored procedure in Sybase which returns
> multiple rows, I
> use this code:
>
> CallableStatement cstmt=null;
> cstmt = conn.prepareCall("{ ? = call p_WEB(?, ?, ?, ?, ?, ?,
> ?, ?, ?, ? )}")
> ;
>
> The resultset has to be registered as an out-parameter by using
> cstmt.registerOutParameter(1,<what goes here>);
>
> Am I right???
>
> My problem is that I cant figure out what to put as the 2nd
> parameter to
> registerOutParameter.
>
> Is there another way to retrive the resultset (with multiple
> rows) from my
> stored procedure.
>
> Please help me...
>
> Regards,
> Mette :-)
>
> ______________________________________________________________
> _____________
> To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources:
http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
***********************************
Mette Larsen
Consultant, Scandpower AS
e-mail: [EMAIL PROTECTED]
Phone: 69 21 27 00
Mob: 99 25 98 77
Direct: 69 21 27 24
Fax: 69 18 44 35
Personal Web & E-mail:
http://www.Mette.Larsen.net
[EMAIL PROTECTED]
***********************************
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
