Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
morph wang
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • 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 !
 
Michael Fitzmaurice
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
  • 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
 
Yogen Vadnere
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • 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{
}
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic