Java & Oracle REF CURSOR

From: HSA <hrushev_at_gmail.com>
Date: 29 May 2007 23:09:08 -0700
Message-ID: <1180505347.901370.300850@k79g2000hse.googlegroups.com>

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. Received on Wed May 30 2007 - 01:09:08 CDT