Re: ResultSet from stored procedure in Oracle

kpaul_at_techna.co.in wrote:

> We are in a desparate need to write stored procedure in Oracle from which
> ResultSet can be retrieved.
>
> In SQL Server 6.5 we can write a simple SQL statement like "Select * from
> temp" within a stored procedure. It has an associated implicit cursor which
> can be retrieved and stored in a RecordSet object in aVB client using ODBC.
>
> But we are facing problems when we try to use the similar technique in
> Oracle. First, it is not possible to write a statement like "Select * from
> temp" within an Oracle procedure; we have to use explicit cursor for that.
> Even though we declare an explicit cursor like ... cursor c1 is select *
> from temp; begin open c1; ...
>
> we cannot find a way so that the content of c1 can be stored in a ResultSet
> through JDBC in java class. The client code looks like following:

JDBC itself AFAIK doesn't support returning ResultSets from stored procedures. I also needed to return ResultSets and I found a solution, but which is proprietary and most likely only works for Oracle DB's.

The Oracle thin driver classes, which I found inside the Oracle/Linux distribution, define additional types, one of them is called OracleTypes.CURSOR. This looks like:

CallableStatement cs = connection.prepareCall("{? = call PACKAGE.FUNC()}");
cs.registerOutParameter(1,OracleTypes.CURSOR); cs.execute();
ResultSet r1 = (ResultSet)cs.getObject(1);

You have to check the documentation of your Oracle JDBC drivers, if this additional data type is supported.

bye
--
Sam Jordan Received on Mon Mar 15 1999 - 06:53:35 CST