Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting a resultSet from a CallableStatement

 
Mike Firkser
Ranch Hand
Posts: 249
Java Oracle PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 249
Java Oracle PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic