Re: Java & Oracle REF CURSOR

On May 30, 8:09 am, HSA <hrus..._at_gmail.com> 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