Getting a resultSet from a CallableStatement

[Mike Firkser]

I'm trying to get a user defined table to be returned from an oracle function. It seems my problem is the type of out parameter I should assign. Here is the oracle function definition:

function test_collection(
      dis_id IN param_dis_table_type,
      caseStatus PARAM_CASESTATUS_TABLE_TYPE,
      reportStatus PARAM_REPORTSTATUS_TABLE,
      start_date varchar2,
      end_date varchar2,
      reportName varchar2
) return STD_AGE_GROUP_TABLE PIPELINED deterministic
as  . . . . .

Here is my java code:

I'm having problems with cs.registerOutParameter. Depending what I put in there, either that line throws an exception ( java.sql.SQLException: java.sql.SQLException: Invalid column type: <some number here>) or the execute() line throws one ( java.sql.SQLException: Invalid SQL type ) The STD_AGE_GROUP_TABLE that is returned is a table defined in Oracle.

      // declaring variables
    Connection conn = null;
	CallableStatement cs = null;
	ResultSet rs = null;
	Integer[] disArray =  {new Integer(9976), new Integer( 8110), new Integer( 8111), new Integer( 9495), , new Integer( 9497), new Integer( 9500), new Integer( 9502), new Integer( 9498), new Integer( 9499), new Integer( 9501), new Integer( 9503)}; 
	String[] csArray = {"REPORT UNDER INVESTIGATION (RUI)", "CONFIRMED"};
	

    String[] rsArray = {"DHSS_APPROVED", "PENDING"};

    String startDate = "01/01/2011";
    String endDate = "08/31/2011";
    String reportName = "STD_AGE_GROUP_STATS";


      Connection conn = OracleDAOFactory.getConnection();
       CallableStatement cs = conn.prepareCall("{ ? := CALL CDRSSDEV.CDRSS_REPORT_NEW.TEST_COLLECTION(?,?, ?,?,?,?) }",  ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
       cs.registerOutParameter(1, < HERE IS WHERE I HAVE THE PROBELM>);
        ArrayDescriptor ad1 = ArrayDescriptor.createDescriptor("CDRSSDEV.PARAM_DIS_TABLE_TYPE", conn);
       ARRAY disSqlArray = new ARRAY(ad1, conn, disArray);
        ((OracleCallableStatement)cs).setArray(2, disSqlArray );
       ArrayDescriptor ad2 = ArrayDescriptor.createDescriptor("PARAM_CASESTATUS_TABLE_TYPE", conn);
       ARRAY csSqlArray = new ARRAY(ad2, conn, csArray);
       ((OracleCallableStatement)cs).setArray(3, csSqlArray);
       out.println("6<br>");
       ArrayDescriptor ad3 = ArrayDescriptor.createDescriptor("PARAM_REPORTSTATUS_TABLE", conn);
       ARRAY rsSqlArray = new ARRAY(ad3, conn, rsArray);
       ((OracleCallableStatement)cs).setArray(4, rsSqlArray);
       cs.setString(5, startDate);
       cs.setString(6, endDate);
       cs.setString(7, reportName);
       cs.execute();
      
       ResultSet rs = (ResultSet)cs.getObject(1);

Any help would be greatly appreciated.

[Martin Vajsar]

As far as I know, pipelined function does not return a cursor (cursor is represented by ResultSet in JDBC). Instead, it can be used as a table in SQL queries. Therefore, you should be able to just select from the function. Lots of examples can be found on the web, eg. http://www.akadia.com/services/ora_pipe_functions.html

You should therefore be able to use just PreparedStatement instead of CallableStatement and use query like

select something from TABLE(CDRSSDEV.CDRSS_REPORT_NEW.TEST_COLLECTION(?,?, ?,?,?,?))

Two notes:

1) Do you really need pipelined function? If a simple function returning a cursor can cover your needs, I'd say that it could be more efficient than a pipelined function.

2) Test the query in SqlPlus first, it may save you lot of time. Passing arrays from SqlPlus might be problematic, so create simple pipelined function first and test with SqlPlus and your Java program, then move on to the version with arrays.

Hope this helps. I didn't ever need a pipelined function, so I won't be able to assist you any further, but in the examples on the web it seems quite straightforward.

[Mike Firkser]

Thank you for the response.

I don't want this to return a cursor, because this will be used with JasperReports, which doesn't seem to handle refcursors. We're just starting to use Jasper and we're figuring out how to pass it Arrays. Currently, we pass delimited strings, and have an Oracle function turn them into a table.

I'll keep plugging away using the info you gave me. Changing it to a PreparedStatement got rid of the errors I had, but now I getting " java.sql.SQLException: ORA-01008: not all variables bound ORA-06512:" so I better me setStuff methods.

Thanks again.