Using REF CURSORS with Java

A REF CURSOR is a cursor variable that contains a pointer to a query result set returned by an OPEN statement. Unlike a static cursor, a REF CURSOR is not tied to a particular query. You may open the same REF CURSOR variable any number of times with the OPEN statement containing different queries; each time, a new result set is created for that query and made available via the cursor variable. A REF CURSOR can also pass a result set from one procedure to another.

Advanced Server supports the declaration of both strongly-typed and weakly-typed REF CURSORs. A strongly-typed cursor must declare the shape (the type of each column) of the expected result set. You can only use a strongly-typed cursor with a query that returns the declared columns; opening the cursor with a query that returns a result set with a different shape will cause the server to throw an exception. On the other hand, a weakly-typed cursor can work with a result set of any shape.

To declare a strongly-typed REF CURSOR:

TYPE <cursor_type_name> IS REF CURSOR RETURN <return_type>;

To declare a weakly-typed REF_CURSOR:

name SYS_REFCURSOR;

.. raw:: latex

  \newpage

Using a REF CURSOR to retrieve a ResultSet

The stored procedure shown in Listing 1.11-a (getEmpNames) builds two REF CURSORs on the server; the first REF CURSOR contains a list of commissioned employees in the emp table, while the second REF CURSOR contains a list of salaried employees in the emp table:

Listing 1.11-a

CREATE OR REPLACE PROCEDURE getEmpNames
(
  commissioned IN OUT SYS_REFCURSOR,
  salaried IN OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN commissioned FOR SELECT ename FROM emp WHERE comm is NOT NULL;
  OPEN salaried FOR SELECT ename FROM emp WHERE comm is NULL;
END;

The RefCursorSample() method (see Listing 1.11-b) invokes the getEmpName() stored procedure and displays the names returned in each of the two REF CURSOR variables:

Listing 1.11-b

public void RefCursorSample(Connection con)
{
  try
  {
    con.setAutoCommit(false);
    String commandText = "{call getEmpNames(?,?)}";
    CallableStatement stmt = con.prepareCall(commandText);
    stmt.setNull(1, Types.REF);
    stmt.registerOutParameter(1, Types.REF);
    stmt.setNull(2, Types.REF);
    stmt.registerOutParameter(2, Types.REF);

    stmt.execute();
    ResultSet commissioned = (ResultSet)stmt.getObject(1);
    System.out.println("Commissioned employees:");
    while(commissioned.next())
    {
      System.out.println(commissioned.getString(1));
    }

    ResultSet salaried = (ResultSet)stmt.getObject(2);
    System.out.println("Salaried employees:");
    while(salaried.next())
    {
      System.out.println(salaried.getString(1));
    }
  }
  catch(Exception err)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    err.printStackTrace();
  }
}

A CallableStatement prepares each REF CURSOR (commissioned and salaried). Each cursor is returned as an IN OUT parameter of the stored procedure, getEmpNames():

String commandText = "{call getEmpNames(?,?)}";
CallableStatement stmt = con.prepareCall(commandText);

The call to registerOutParameter() registers the parameter type (Types.REF) of the first REF CURSOR (commissioned) :

stmt.setNull(1, Types.REF);
stmt.registerOutParameter(1, Types.REF);

Another call to registerOutParameter() registers the second parameter type (Types.REF) of the second REF CURSOR (salaried) :

stmt.setNull(2, Types.REF);
stmt.registerOutParameter(2, Types.REF);

A call to stmt.execute() executes the statement:

stmt.execute();

The getObject() method retrieves the values from the first parameter and casts the result to a ResultSet. Then, RefCursorSample iterates through the cursor and prints the name of each commissioned employee:

ResultSet commissioned = (ResultSet)stmt.getObject(1);
while(commissioned.next())
{
  System.out.println(commissioned.getString(1));
}

The same getter method retrieves the ResultSet from the second parameter and RefCursorExample iterates through that cursor, printing the name of each salaried employee:

ResultSet salaried = (ResultSet)stmt.getObject(2);
while(salaried.next())
{
  System.out.println(salaried.getString(1));
}