Returning a Ref Cursor as a column in a query

An interesting technique I came across recently, is the ability to return an embedded ref cursor as column in an SQL statement.

For instance, if we have a table of employees:

create table employees (emp_id integer not null,
                        emp_name varchar2(100) not null,
                        primary key (emp_id));

Each employee can have multiple phone numbers, so we have another table employeephonenum:

create table employee_phone_num (emp_id     integer      not null,
                                 phone_type varchar2(10) not null,
                                 phone_num  varchar2(20) not null); 

If we put some data into the tables, such that each employee has two phone numbers:

insert into employees values (1, 'Bob Smith');
insert into employee_phone_num values (1, 'home', '1234656');
insert into employee_phone_num values (1, 'mobile', '1234656');

insert into employees values (2, 'Jane Smith');
insert into employee_phone_num values (2, 'home', '1234656');
insert into employee_phone_num values (2, 'mobile', '1234656');

commit;

To retrieve the details for an employee and its phone numbers, normally you would run two queries or join the two tables and then deal with getting two rows back per employee. However, there is another interesting way:

select emp_name,
       cursor(select phone_type,
                     phone_num
              from employee_phone_num epn
              where epn.emp_id = e.emp_id)
from employees e;

EMP_NAME        CURSOR(SELECTPHONE_T
--------------- --------------------
Bob Smith       CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

PHONE_TYPE PHONE_NUM
---------- --------------------
home       1234656
mobile     1234656

Jane Smith      CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

PHONE_TYPE PHONE_NUM
---------- --------------------
home       1234656
mobile     1234656

This query contains a sub-cursor for each row in the form of a ref cursor. In sqlplus, this isn't very useful, but in Java it might be.

In Java, you can call getCursor on the relevant column of the result set, and it returns another result set. This could be very useful if you are attempting to render a HTML page for an employee or list of employees for example.

If you do use this, you have to be very careful. For each row Oracle returns, it will open another cursor for the inline ref cursor, whether you fetch from it or not in Java. Infact, it depends on the fetch size - if you set the fetch size to 100, then Oracle will prepare 100 rows to be sent to the Java program in a batch, which will require opening 100 cursors all at once.

If the Java code stops fetching before it gets to end of the result set, these cursors will still remain open until the main query result set is closed and the Java garbage collector cleans up the variables.

So while I think this is a nice feature, I also think it is a dangerous feature, especially for large result sets.

The Java code to fetch the details from this embedded cursor is below.

import java.sql.*;
import oracle.jdbc.*;

public class Fetch {

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException,     InterruptedException
    {
        DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());

        String oracle_url =     "jdbc:oracle:thin:@//127.0.0.1:1521/local11gr2.world";

        Connection conn =
            DriverManager.getConnection(oracle_url,"sodonnel","sodonnel");

    PreparedStatement stmt;
    ResultSet rset;

    stmt = conn.prepareStatement("select emp_name, "+    
                                            "cursor(select     phone_type, phone_num "+
                                     "            from     employee_phone_num epn "+
                                                  " where     epn.emp_id = e.emp_id) "+
                                     "from employees e");

    rset = stmt.executeQuery();
    while (rset.next()) {
        System.out.println(rset.getString(1));
        ResultSet numbers = ((OracleResultSet)rset).getCursor(2);
            while (numbers.next()) {
            System.out.println("    "+numbers.getString(1)+"     "+numbers.getString(2));
        }
        numbers.close();
    }
    stmt.close();
    }
}