Andrej Koelewijn

I’ve noticed in my  web logs that the item i wrote about ref cursors gets a lot of hits from google. As this item is only a link to a dutch article, i thought it might be usefull to provide a translation:

There are different methods  to get data out of your Oracle database when using java. The most common method is to use JDBC. Other options include Oracle business components for java (BC4J) and Toplink.  Database communication can also be handled by a j2ee container, when you use container managed persistence for entity beans. A disadvantage of using jdbc is that the programmer needs java and sql knowledge.

The example below shows how jdbc is usually used. You instantiate a statement object for a query, you specify the parameter values, execute the query and then you loop through the rows in the resultset.

String usersSql = "select username, user_id, created from all_users";
PreparedStatement stmt = _connection.prepareStatement(usersSql);
ResultSet rset = stmt.executeQuery();
while (rset.next()){
 String username = rset.getString(1);
 BigDecimal userId = rset.getBigDecimal(2);
 Date created = rset.getDate(3);
}
rset.close();
stmt.close();

As mentioned, a disadvantage of this approach is that the programmer needs to know both sql and java. And to write performing queries, the programmer needs database specific knowledge, for example about oracle hints which can be using in queries.

By using pl/sql ref cursors you can avoid that the java programmer needs to have a lot of sql knowledge. All queries can be defined in oracle package in the database, and the java programmer can use these queries by calling pl/sql stored procedures. The stored procedure will return a  ref cursor.  The result of the ref cursor can be read by normally looping of  a resultset.

The example below shows how a ref cursor is defined in a pl/sql package. The result of calling the pl/sql function getusers is a pointer to a cursor.

CREATE OR REPLACE PACKAGE cursors_pkg
IS
  TYPE refcursortype IS REF CURSOR;
  FUNCTION getusers RETURN refcursortype;
END;
/

CREATE OR REPLACE PACKAGE BODY cursors_pkg
IS
  FUNCTION getusers RETURN refcursortype
  IS alluserscursor refcursortype;
BEGIN
  OPEN alluserscursor FOR
    SELECT username
    ,      user_id
    ,      created
    FROM   all_users;
    RETURN alluserscursor;
  END;
END;

After the ref cursor has been defined, it can easily be used by java, as the following example shows:

String usersSql = "{ ? := cursors_pkg.getUsers; }";
CallableStatement stmt = _connection.prepareCall(usersSql);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
ResultSet rset =((OracleCallableStatement) stmt).getCursor(1);
while (rset.next()){
 String username = rset.getString(1);
 BigDecimal userId = rset.getBigDecimal(2);
 Date created = rset.getDate(3);
}
rset.close();
stmt.close();

As you can see, the difference with the previous example isn’t very big. We are still using a resultset to loop over all the records. The only difference is that the query has been defined in the pl/sql pacakge. The only sql code you are left with is the function call. This is a lot easier to write than sql queries (no hints needed, no wrong joins possible), and a lot more secure, as you don’t have to hand out direct access to tables, only access to the pl/sql code.

The example uses oracle specific java classes, but you can also rewrite it, so that it doesn’t use anything oracle specific.  Postgresql, for example, also supports ref cursors, so if you want portable java code, be sure not to use the oracle specific classes.

In the example above, a weakly typed cursor was used. You can also use strongly typed cursors, e.g., by specifying a record type in the package specification. This is shown in the following example:

CREATE OR REPLACE PACKAGE cursors_pkg IS
  TYPE userstype IS RECORD (
    username VARCHAR2(30)
  , userid NUMBER
  , created DATE
  );
  TYPE userscursortype IS REF CURSOR RETURN userstype;
  FUNCTION getusers RETURN userscursortype;
END;

I’ve done some tests, to see if using ref cursors has a negative impact on performance. This doesn’t seem to be the case. So using ref cursors seems to be usefull when you have a team with java specific and oracle specific programmers.