Table of Contents Previous Next


7 Advanced JDBC Connector Functionality : 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.
TYPE <cursor_type_name> IS REF CURSOR RETURN <return_type>;
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:
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:
A CallableStatement prepares each REF CURSOR (commissioned and salaried). Each cursor is returned as an IN OUT parameter of the stored procedure, getEmpNames():
The call to registerOutParameter() registers the parameter type (Types.REF) of the first REF CURSOR (commissioned) :
Another call to registerOutParameter() registers the second parameter type (Types.REF) of the second REF CURSOR (salaried) :
A call to stmt.execute() executes the statement:
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:
The same getter method retrieves the ResultSet from the second parameter and RefCursorExample iterates through that cursor, printing the name of each salaried employee:

7 Advanced JDBC Connector Functionality : 7.4 Using REF CURSORS with Java

Table of Contents Previous Next