File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to create Oracle stored procedure and return ResultSet as OUT param...? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to create Oracle stored procedure and return ResultSet as OUT param...?" Watch "How to create Oracle stored procedure and return ResultSet as OUT param...?" New topic
Author

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

morph wang
Ranch Hand

Joined: Aug 23, 2001
Posts: 38
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 !

<todo>
Michael Fitzmaurice
Ranch Hand

Joined: Aug 22, 2001
Posts: 168
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


"One good thing about music - when it hits, you feel no pain" <P>Bob Marley
Yogen Vadnere
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
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{
}


Yogen Vadnere
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
 
 
subject: How to create Oracle stored procedure and return ResultSet as OUT param...?