Stored Procedures for Java Programmers
Subject:   How to return a ResultSet from a stored procedure w/o using OUT parameter
Date:   2003-12-03 01:11:17
From:   anonymous2

I know that we can return resultsets from oracle stored procedures using OUT parameters. So using the class CallableStatement we can get the ResultSet object.

The most common code would be:

CallableStatement cstmt = connection.prepareCall(.....);
cstmt.registerOutParameter(index, oracle.jdbc.driver.OracleTypes.CURSOR);
ResultSet rs = (ResultSet)cstmt.getObject(index);

I have 3 questions:

1. Is there any other way to get ResultSet from an oracle stored procedure (other than OUT parameter)?

2. Since CallableStatement extends PreparedStatement, can I call executeQuery() on CallableStatement object to get the resultset? Ideally, I would want the resultset just as in the case of PreparedStatement (pstmt.executeQuery() returns the resultset)

3. Can we return more than 1 resultset from a stored procedure?

I am using oracle (8i) thin db drivers ( file).