Passing a REF CURSOR as a java method input parameter
440432Nov 21 2007 — edited Dec 11 2007I 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