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.
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(?,?, ?,?,?,?))
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.
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.