• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to create Oracle stored procedure and return ResultSet as OUT param...?

 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How can I create an Oracle stored procedure which return a ResultSet , so I can retrieve the ResultSet in Java like this:
CallableStatement cs = conn.prepareCall("{call PROC_XXX(?, ?)}");
cs.setInt(1, 25);// this is a parameter pass in as some condition
cs.registerOutParameter(2, java.sql.Types.OTHER);// this is the OUT parameter which will return as a ResultSet
cs.execute();
ResultSet rset = (ResultSet)cs.getObject(2);
//....Now I can go through the 'rset' ...
...
Will be very appreciated if any one can give out a sample, thanks advance !
 
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi
You should use <code>executeQuery()</code> rather than <code>execute()</code>. This returns a ResultSet object, which does not need to be registered as an output parameter.
Just set your input params as normal then use something like:
<code>
ResultSet results = cs.executeQuery();
</code>
You will obviously need to amend the number of input param placeholders and parameters expected by the underlying stored proc.
Hope this helps
Michael

------------------
"One good thing about music - when it hits, you feel no pain"
Bob Marley
 
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,
Here is the code to get the resultset (oracel cursor as out parameter)
Note : I m using oracle thin driver.

String s = "{CALL GET_DETAIL(?, ?)}";
CallableStatement c = con.prepareCall(s);
try{
c.registerOutParameter(1, Types.NUMERIC);
//OracleType and OracleCallableStatement classes are
//provided by driver.
c .registerOutParameter(2,OracleTypes.CURSOR);
cst.execute();
ResultSet cRst = ((OracleCallableStatement)c).getCursor(2);
}catch(SQLException ex{
}
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/basic/basic.htm
The above link has sample code for using/getting refcursors from stored procedures (look for "Query Variables (Refcursor)" link for the sample code.
Jamie
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic