ajay,
i think you will find that you *can* return result sets from
oracle stored procedures [+-3 years]. use a REF CURSOR within
the stored procedure and cast it back to a ResultSet after execution.
only recently have Oracle added the functionality to build a
REF CURSOR from loosley bound SQL [+-8.1.6] which i hope is
where tim may be a little confused.
there are a lot of examples of this around so check out the
Oracle/technet sites for more details.
regards.
ray.
-----Original Message-----
From: A mailing list for discussion about Sun Microsystem's Java Servlet
API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of Tim
Stephenson
Sent: 20 April 2000 15:46
To: [EMAIL PROTECTED]
Subject: Re: Resultset from stored procs
Welcome to the wonderful world of Oracle!
Oracle stored procedures *cannot* return result sets - try this using
sqlplus or some other tool before you try it from the java to see what i
mean. The procedure will compile but with errors, use 'show errors' to see
what they are, or just try executing the stored proc.
So what is the answer ? You can try writing the procedure such that it
populates a table with result set which you then obtain by a separate query.
In this case be VERY careful about what other users will get when they try
the same operation simultaneously (include a username in the result set or
something).
Alternately just do multiple queries from the java code. I know this is less
efficient, but hey what can I say - use Sybase!
rgds, tim
----- Original Message -----
From: "AJAY KATRE" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 19, 2000 1:43 PM
Subject: Resultset from stored procs
>
>
> Hi! Everybody,
>
> I know that this might be off topic but I'm doing servlet prog and faced
> this
> prob in the midst of it.
>
> I and using Oracle 8i a backend.
>
> I want to know how I can return multiple records that are formed as result
> of
> processing in the SP.
>
> I call the SP as follows:
> CallableStatement callSt = connection.prepareCall("CALL PADD(.....)");
> callSt.registerOutParameter(1, java.sql.Types.INTEGER);
> rsp = callSt.executeQuery();
>
> At the end of the above mentioned calls I want to have rsp (my resultset)
> to
> be populated with multiple records that are formed in the SP.
> At present I write the values in a temp table, read the values by another
> call
> to executeQuery and delete values from this temp table.
>
>
> Please suggest modifations to the stored procedure and JDBC code to give
> better control on what a call to executeQuery can return when used as
> above.
>
> Thanks in anticipation,
> Ajay.
>
>
>
> ____________________________________________________________________
> Get free email and a permanent address at http://www.netaddress.com/?N=1
>
>
___________________________________________________________________________
> 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
