Re: Returning recordsets from JDBC

From the Oracle JDBC documentation :

The Oracle JDBC driver supports bind variables of type REFCURSOR. A REFCURSOR is represented by a JDBC ResultSet. Use the getCursor method of the CallableStatement to convert a REFCURSOR value returned by a PL/SQL block into a ResultSet. JDBC lets you call a stored procedure that executes a query and returns a results set. Cast the corresponding CallableStatement to oracle.jdbc.driver.OracleCallableStatement to use the getCursor method.

Importing classes from the oracle.jdbc.driver package makes programs more readable. Here is a simple example. The samples subdirectory of the distribution has additional examples.

import oracle.jdbc.driver.*;
...
  CallableStatement cstmt;
  ResultSet cursor;    

  // Use a PL/SQL block to open the cursor   cstmt = conn.prepareCall

             ("begin open ? for select ename from emp; end;");

  cstmt.registerOutParameter (1, OracleTypes.CURSOR);   cstmt.execute ();
  cursor = ((OracleCallableStatement)cstmt).getCursor (1);

  // Use the cursor like a normal ResultSet   while (cursor.next ())
    {System.out.println (cursor.getString (1));}

Aik Khoon wrote:
>
> Hi,
>
> I have been trying to retrieve resultsets with multiple columns (e.g. 4)
> using
> JDBC and PL/SQL function routine but no records showed up even though
> I can execute the function successfully from SQL*Plus.
>
> Question :
> 1. I am returning the resultsets using CURSOR through a function routine.
>
> create or replace function getallresult()
> return types.cursorType
> l_cursor types.cursorType;
> as
> open l_cursor ......select ename, eno, address, phone from tablex;
> return l_cursor;
> end;
>
> Is the a limit to the size of the column...?
> I'm returning > 200 bytes through the cursor
>
> 2. Is this limitation pertaining to function?
> I can manage to return recordsets with one column.
>
> 3. Can someone suggest a more effective way?
>
> TIA,
>
> Aik khoon.

--
ÐÏࡱá Received on Thu Jul 01 1999 - 08:50:15 CDT