1 Reply Latest reply: Jun 7, 2013 4:24 PM by dsurber RSS

    Return custom implemented ResultSet as REF CURSOR from a java stored proc

    martani

      Hello,

      We would like to return a collection of objects from a java stored procedure to a PL/SQL code as a REF CURSOR. For this we want to implement a custom OracleResultSet object which handles retrieval of these objects.

      So we basically have these classes for the ResultSet:

      public class CustomResultSet implements OracleResultSet {       /* METHODS */ }
      public class CustomResultSetMetaData implements ResultSetMetaData {

      In my stored procedure function, I do something as follows:

      public static java.sql.ResultSet ReturnCustomResultSet() throws SQLException {     //Initiliaze connection etc...     CustomResultSet rs = new CustomResultSet ();     //Fill rs and other operations     return rs; }

      The PL/SQL call spec is as follows:

      CREATE OR REPLACE FUNCTION RETURN_CUSTOM_RESULTSET   RETURN SYS_REFCURSOR   AS LANGUAGE JAVA   NAME 'MyClass.ReturnCustomResultSet()         return java.sql.ResultSet';

      When I try to call this code; I get the following exception:

      Exception in thread "Root Thread" java.lang.ClassCastException at oracle.jdbc.driver.T2SConversions.returnCursor(T2SConversions.java:351)

      So my question is: is there any special setup I am missing to make this work? and if Oracle won't be satisfied unless the ResultSet is returned from some prepareStatement method, what means are there to return a collection of objects (a List<String>) and get it consumed as a REF CURSOR in Oracle?


      Thank you.