Two Laptop Bag*
The moose likes JDBC and the fly likes Problem in getting multiple resultsets from Oracle Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem in getting multiple resultsets from Oracle Stored Procedure" Watch "Problem in getting multiple resultsets from Oracle Stored Procedure" New topic
Author

Problem in getting multiple resultsets from Oracle Stored Procedure

sandeep pandey
Greenhorn

Joined: Nov 09, 2001
Posts: 1
Hi java gurus,
I'm using Websphere 3.5 standard edition with Oracle 8i as backend. I'm trying to access multiple resultsets thru oracle stored procedure.
-----------------------------
PROCEDURE add_Person (
pPersonId IN INTEGER,
pCountry OUT tCursor,
pPerson OUT tCursor) as

BEGIN
OPEN pCountry FOR
SELECT CTRY_CODE,DESC FROM COUNTRY;
OPEN pPerson FOR
SELECT * FROM PERSONS WHERE PERSON_ID = pPersonId;
END;
END;
---------------------
This procedure works well thru Oracle DBA Studio, but when I call it thru my java program,
-------------------------
Callable Statement stmt = connection.prepareCall("{call add_person(?,?,?)}");
stmt.setInt(1, pId);
stmt.registerOutParameter(2,OracleTypes.CURSOR);
stmt.registerOutParameter(3,OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
----------------------
I get a ClassCastException at runtime at last line above.
If any body has ever worked on Websphere+Oracle8i with such kinda stored procedures (returninmg multiple resulsets), pls help me out of this trouble..
Thanks for your time
sandeep
Salman Zaidi
Greenhorn

Joined: May 09, 2003
Posts: 1
Use ResultSet rs = (ResultSet)stmt.getObject(2);
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem in getting multiple resultsets from Oracle Stored Procedure
 
Similar Threads
Stored procedure which returns array
Closing cursor in a stored procedure when invoked from a callable statement
What am i doing wrong here? ( Stored Procedure REF CURSOR Problem)
Call Stored Procedure Returning Cursor as Output
JDBC and stored procedure - Please Help!!!