How to access REF CURSOR Data in Java ?

In Java, a REF CURSOR is known as a ResultSet object and can be accessed as shown below:

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

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select sname from student; 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));} 

In the above 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. 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 API, and returns the REF CURSOR into a ResultSet object.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s