11

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?

Yoni
  • 10,171
  • 9
  • 55
  • 72

2 Answers2

6

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

martsraits
  • 2,915
  • 3
  • 27
  • 25
  • I agree it is ugly, but I didn't find anything better than this for now – Yoni Apr 05 '11 at 14:57
  • This is just too ugly! Would havee been ok if it was a college project, but for any real projects, I beg you not to use this! :) – anuragw Apr 23 '13 at 07:25
1

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

martsraits
  • 2,915
  • 3
  • 27
  • 25
  • unfortunately it doesn't work. The CallableStatement object throws an ora-17004 exception when trying to register the out parameters with Types.OTHER. – Yoni Jan 15 '09 at 22:08