Accessing REF CURSOR Data

In Java, a REF CURSOR is materialized as a ResultSet object and can be accessed as follows:

In the preceding example:

  1. A CallableStatement object is created by using the prepareCall method of the connection class.
  2. The callable statement implements a PL/SQL procedure that returns a REF CURSOR.
  3. As always, the output parameter of the callable statement must be registered to define its type. Use the type code OracleTypes.CURSOR for a REF CURSOR.
  4. The callable statement is run, returning the REF CURSOR.
  5. The CallableStatement object is cast to OracleCallableStatement to use the getCursor method, which is an Oracle extension to the standard JDBC application programming interface (API), and returns the REF CURSOR into a ResultSet object.

Example 6-9 Accessing REF Cursor Data in Java

import oracle.jdbc.*;
...
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));}