• 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
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

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
 
Note to self: don't get into a fist fight with a cactus. Command this tiny ad to do it:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic