File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes A real challenge... resultsets of rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "A real challenge... resultsets of rows" Watch "A real challenge... resultsets of rows" New topic

A real challenge... resultsets of rows

Joe Angott

Joined: Oct 13, 2000
Posts: 7
Ok, here's the skinny...
I have an Oracle stored function in a package that I call feeding it a single parameter. This is done using a callable statement. It then sends me back multiple rows of multiple columns of data.
Question #1: How do I define my out parameter portion of my call so that it is understood by the JDBC that I will be getting back a rowset?
Question #2: Once I get back my mountain of data, how do I go about parsing out the fields in each row? I think I might have a grasp on this one, but would like to know for sure. I think if I go through the result set and parse based on an index corisponding to the position of the field in that row it will give me my desired results.
I know this is probably not an easy one, but I would love for someone to prove me wrong.
Joe Angott

Joined: Oct 13, 2000
Posts: 7
Well I did some more extensive research and found this example (shown in part) from OTN, part of Oracle's online documentation.

try {

// If resultset is open, close it.
if (m_resultSet != null)

// Prepare a Callable Statement, to make a call the PLSQL function demo_refcursor.getRefCursor, and obtain the returned REFCURSOR
CallableStatement l_call =
m_connection.prepareCall("{? = call " + "demo_refcursor.getRefCursor }");

// set the Out Parameter type to be of type CURSOR
l_call.registerOutParameter(1, OracleTypes.CURSOR);
l_call.execute(); // Execute the statement

// Cast the returned parameter, (defined as type, OracleTypes.CURSOR) to a JDBC result-set. setFirstFive displays the first five rows of the returned REFCURSOR
m_resultSet = (ResultSet)l_call.getObject(1);

// Display the next five records from the newly opened resultset
// Enable the "Next Five Records" button
} catch (SQLException ex) {
// Trap SQL errors
m_GUI.putStatus("Error running package demo_refcursor:");

That's the guts of the Oracle Example, not too bad considering the lack of examples in most of Oracle's database reference material. Now here is the end result of my and other's research, and the answer to my own question. I hope it can help someone else.
import java.sql.*;
import oracle.jdbc.driver.*; // comes from Oracle's
try {
CallableStatement retriveOrderStatusCall = conn.prepareCall("{? = call Order_Object.query_status_object(?) }");
retriveOrderStatusCall.setString(2, orderId);
retriveOrderStatusData =(ResultSet)retriveOrderStatusCall.getObject(1);

System.out.print("Orderid "+rs.getString(1)+ " ");
System.out.print("Product "+rs.getString(2).trim()+ " ");
System.out.println("Status "+rs.getString(3).trim()+ " ");

There's the skinny, I hope this helps someone with a similar delima. If you were searching for the answer to help me, I appreciate the effort.
[This message has been edited by Joe Angott (edited March 06, 2001).]
I agree. Here's the link:
subject: A real challenge... resultsets of rows
It's not a secret anymore!