Re: Java & Oracle REF CURSOR

On May 30, 8:09 am, HSA <hrus...@xxxxxxxxx> wrote:
Hi, All !

I have an Oracle stored procedure which returns some data to Java
program by using REF CURSOR
and there is an interesting problem exists: sometimes stored procedure
must return NULL instead of
REF CURSOR and when it does that then "on Java side" it's impossible
to use ((OracleCallableStatement)cstmt).getCursor(1).
This call causes "java.sql.SQLException: Cursor is closed". I think
it's because ResultSet object
can't be constructed from NULL cursor.
My question is: How can I determine that NULL cursor was returned by
stored procedure?

Sample code is:
String sQuery = "begin ? := get_data(?, ?); end;";

CallableStatement cstmt = conn.prepareCall(sQuery);
try {
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setLong(2, nParam2);
cstmt.setLong(3, nParam3);
cstmt.execute();

ResultSet rset =
((OracleCallableStatement)cstmt).getCursor(1);

Thanks.

Fix the procedure, instead of adding extra kludges.
Why can't you just
a) return an empty cursor
b) return an extra flag for this special situation

In short: why do you screw the cursor?

--
Sybrand Bakker
Senior Oracle DBA

.

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... these words at the beginning before sending them to the cursor. ... Looking for a FAQ on Indexing Services/SQL FTS ... >>Create table Noise ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SQL stored procedure not working.
    ... If I do not use that, how can I get the total record count? ... >> I create a stored procedure and a asp page. ... Even if you could specify the cursor type there, ... > interested in the RETURN value, you do not need to use a Command object. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)
  • RE: how to call a stored procedure that returns cursor??
    ... | Content-Class: urn:content-classes:message ... | i have a stored procedure that returns a cursor. ... JDBC directly. ...
    (microsoft.public.sqlserver.jdbcdriver)