Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Passing a REF CURSOR as a java method input parameter

440432Nov 21 2007 — edited Dec 11 2007
I have seen numerous examples where java code can create a CallableStatement and retrieve a ref cursor as an OUT parameter or function result. This is an example of those I have seen.


// Prepare a Callable Statement, to make a call to the PLSQL function
// demo_refcursor.getRefCursor, and obtain the returned REFCURSOR
CallableStatement stmt =
connection.prepareCall("{? = call demo_refcursor.getRefCursor }");

// Set the Out Parameter type to be of type CURSOR
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute(); // Execute the statement

// Cast the returned parameter, (defined as type, OracleTypes.CURSOR)
// to a JDBC result-set.
resultSet = (ResultSet)stmt.getObject(1);


But is it at all possible to pass a ref cursor opened in plsql as a parameter to a java stored procedure? The main problem is: what is the java class of the input parameter for a ref cursor?
The stmt.registerOutputParameter(1, OracleTypes.cursor) command above must be setting up the data type mapping with the OracleTypes.cursor constant so that stmt.getObject(1) command can be cast as a ResultSet.

I am not a java expert so there could easily be something I am missing. Though, I've searched through the onion layers of java class docs and tried several attempts but none works thus far.

A few attempts:

public static String testRef (ResultSet cur)...
public static String testRef (oracle.jdbc.oracore.OracleType cur)...
public static String testRef (oracle.sql.STRUCT cur)...
public static String testRef (oracle.jdbc.oracore.OracleTypeREF cur)...

I find it odd that java must call plsql to get a ref cursor but that java can not seemingly accept a ref cursor parameter.

Thanks
Andrew
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2008
Added on Nov 21 2007
14 comments
8,296 views