On returning a collection of user defined objects from PL/SQL to Java – shortcut using a sys_refcursor

Oracle 8.0 introduced Objects into the Oracle database, back in 1996. There has been a lot of ridicule for the OO features in the Oracle database, some of it deserved as that first implementation was a laugh. Two things at least can be said about objects or user defined Types in Oracle: they have matured an awful lot in 9i and 10g. And they play an important role in the integration between PL/SQL and SQL. For example Table Functions – the feature that allow us to access PL/SQL functions as if they were tables inside Select statements – are fueled by Types.

One thing that is not trivial is how to deal with Objects (type instances) and Collections of Objects in environments that do not know about the TYPE definition. For example: if we call from our own PL/SQL program unit a PL/SQL function or procedure that returns a Collection of Objects of a type that we do not know about, it can be somewhat complex to deal with. And perhaps we do not care all that much for the Collection and the Type. Mapping Objects and Collections of Objects is even less trivial. Using JPublisher and the SQLData or ORAData interfaces we can map Java Classes to Oracle TYPEs. Using the sql.STRUCT is easier if we do not have Java Class counterparts for the Oracle TYPE. However, we can make our lives even less complicated.

In this article I will briefly discuss how we can create a PL/SQL function that wraps a Collection of Oracle TYPE Objects in a SYS_REFCURSOR. Our Java code (or PL/SQL invoker for that matter) can simply process that cursor as if it were based on a real table or view!

Suppose we have the following type definititions in our database:

create type rss_item_type is object
 ( title    varchar2(2000)
 , link     varchar2(200)
 , pubDate  varchar2(200)
 , author   varchar2(200)
 , category varchar2(500)
 , description varchar2(4000)
 )
 /
 
create type rss_item_type_tbl is 
table of rss_item_type
/

Based on these types, we have a PL/SQL function that returns a Collection of RSS_FEED_ITEMS:

 create or replace 
 function get_rss_titles 
 return string_tbl 

From our Java code we are very interested in getting hold of that Collection. However, we do not know exactly how to handle those Oracle TYPE objects nor are we really adept at dealing with Collections and besides, all we need are the title and the publication date from the RSS ITEMS. We do not have full blown counterpart objects in our Java Code.

We can make this easy on ourselves by writing Java code that only needs to process a ResultSet that contains the same six fields in each row that our RSS_ITEM_TYPE contains. In fact, our code only needs to read the first and third String value from each record in the ResultSet:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class GetRssItems {

public static void main(String[] args) throws Exception {
    DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl"    , "up", "up");
    String plsqlCall = "BEGIN ? :=  get_rss_items; end;";
    OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall(plsqlCall);
    cstmt.registerOutParameter(1, OracleTypes.CURSOR);
    cstmt.execute();
    ResultSet rset = (ResultSet)cstmt.getObject(1);
    for(int i = 0;  rset.next(); i++ )
      System.out.println(  rset.getString(1)+ " (on " +rset.getString(3)+")" );
  }
}

The main method creates a connection to the database schema that holds the function get_rss_items. Note: this is a quite horrible and outdated way to connecting to a database through JDBC. For the purpose of this article, it will do though.

The get_rss_items function returns a SYS_REFCURSOR that is converted into a java.sql.ResultSet, the same beast returned by normal Statements in JDBC. So we know how to process that set by simply iterating over the records it contains and picking the two fields we are interested in from each record.

The results from this piece of code look like this:

Keith Richards 'tree fall' injury (on Sat, 29 Apr 2006 22:04:48 GMT)
Protesters demand end to war  (on Sat, 29 Apr 2006 20:26:34 EDT)
Sudan peace deal's deadline approaches (on Sat, 29 Apr 2006 22:23:03 EDT)
Indian hostage killed in Afghanistan (on Sun, 30 Apr 2006 05:13:45 EDT)
Bomb explodes near Nigerian refinery (on Sun, 30 Apr 2006 04:59:36 EDT)
Leftist trio rejects US trade (on Sun, 30 Apr 2006 05:07:24 EDT)
Homeowners supply half of day laborers' work  (on Sat, 29 Apr 2006 21:02:06 EDT)
Dead soldier found in hotel air conditioner  (on Sun, 30 Apr 2006 01:08:39 EDT)
Economist, chronicler of affluent society dies at 97 (on Sun, 30 Apr 2006 02:40:19 EDT)
...

The missing link is the function get_rss_items:

create or replace 
function  get_rss_items
 return sys_refcursor
 as
   
   l_cursor sys_refcursor;
 begin
   open l_cursor
   for  select *
        from   table( get_rss_items_tbl )
        ;
   return l_cursor;
 end;

This function translates the Collection returned by get_rss_items_tbl into a SYS_REFCURSOR.

We can completely by-pass the wrapper PL/SQL function by including the functionality it contains directly in our JDBC CallableStatement:

...
    DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection
                      ( "jdbc:oracle:thin:@localhost:1521:orcl"
                      , "up", "up");
    String plsqlCall = "BEGIN "
                     + "  open ? for  select * "
                     + "              from   table( get_rss_items_tbl );"
                     + "END;";

    OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall(plsqlCall);
    cstmt.registerOutParameter(1, OracleTypes.CURSOR);
    cstmt.execute();
    ResultSet rset = (ResultSet)cstmt.getObject(1);
    for(int i = 0;  rset.next(); i++ )
      System.out.println(  rset.getString(1)+ " (on " +rset.getString(3)+")" );
  }
}