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);
.
.
.
cstmt.execute();
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 (classes12.zip file).

cheers,
Nilesh