CODEWITHSUNDEEP
javaoracle-database
Yoni
Yoni

Reputation: 10321

Using Oracle ref cursor in Java without Oracle dependency

According to google and some other sources (e.g., http://www.enterprisedt.com/publications/oracle/result_set.html), if I want to call a stored-function that returns a ref cursor, I need to write something like this in order to access the ResultSet:

String query = "begin ? := sp_get_stocks(?); end;";
CallableStatement stmt = conn.prepareCall(query);
// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
stmt.setFloat(2, price);
// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

Is there anyway to do it without introducing the compile-time dependency on Oracle. Is there a generic alternative to OracleTypes.CURSOR?

Upvotes: 12

Views: 7989

Answers (3)

Diego1974
Diego1974

Reputation: 419

Also looking for removal of OracleTypes.CURSOR in jdbc call to Oracle stored procedure.

Starting from Java8/JDBC4.2, "CURSOR" it's available in Java SQL types as java.sql.Types.REF_CURSOR (see: here )

Taken from this excellent answer on how to call an Oracle Stored Procedure returning a ResultSet when cast (but remember: CLOSE statement/resultset after usage or Oracle Cursor will remain open and after some calls you will get a ORA-01000 error)

Upvotes: 1

martsraits
martsraits

Reputation: 3015

Have you tried java.sql.Types.OTHER? It might work. API says, it's for database specific types.

Upvotes: 1

martsraits
martsraits

Reputation: 3015

Constant OracleTypes.CURSOR is -10. Quite ugly solution but you can just write -10 there or create your own constant which value is -10.

Upvotes: 6

Related Questions