7.4 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;

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

}