File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Getting a resultSet from a CallableStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Getting a resultSet from a CallableStatement" Watch "Getting a resultSet from a CallableStatement" New topic

Getting a resultSet from a CallableStatement

Mike Firkser
Ranch Hand

Joined: Oct 21, 2003
Posts: 248

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:

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.

Any help would be greatly appreciated.

Mike Firkser
Rutgers '84
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

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.

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
Ranch Hand

Joined: Oct 21, 2003
Posts: 248

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.
I agree. Here's the link:
subject: Getting a resultSet from a CallableStatement
It's not a secret anymore!