Using REF CURSORS with Java v42.7.3.1

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 isn't tied to a particular query. You can 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 by way of the cursor variable. A REF CURSOR can also pass a result set from one procedure to another.

EDB Postgres Advanced Server supports the declaration of both strongly typed and weakly typed REF CURSOR variables. A strongly typed cursor must declare the shape (the type of each column) of the expected result set. You can use only 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 causes the server to return 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;

Using a REF CURSOR to retrieve a ResultSet

The stored procedure shown in the following (getEmpNames) builds two REF CURSOR variabes on the server. The first REF CURSOR contains a list of commissioned employees in the emp table. The second REF CURSOR contains a list of salaried employees in the emp table:

CREATE OR REPLACE PROCEDURE getEmpNames
(
  commissioned OUT SYS_REFCURSOR,
  salaried 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 shown in the following invokes the getEmpName() stored procedure and displays the names returned in each of the two REF CURSOR variables:

public void RefCursorSample(Connection con)
{
  try
  {
    con.setAutoCommit(false);
    String commandText = "{call getEmpNames(?,?)}";
    CallableStatement stmt = con.prepareCall(commandText);
    stmt.registerOutParameter(1, 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 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.registerOutParameter(1, Types.REF);

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

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));
}

• On this page
• Using a REF CURSOR to retrieve a ResultSet