Win a copy of Terraform in Action this week in the Cloud forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Getting a resultSet from a CallableStatement

 
Ranch Hand
Posts: 249
Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic