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

3

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<br /> ( title    varchar2(2000)<br /> , link     varchar2(200)<br /> , pubDate  varchar2(200)<br /> , author   varchar2(200)<br /> , category varchar2(500)<br /> , description varchar2(4000)<br /> )<br /> /<br /> <br />create type rss_item_type_tbl is <br />table of rss_item_type<br />/<br />&nbsp;

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

 create or replace <br /> function get_rss_titles <br /> return string_tbl <br />&nbsp;

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;<br />import java.sql.DriverManager;<br />import java.sql.ResultSet;<br /><br />import oracle.jdbc.OracleCallableStatement;<br />import oracle.jdbc.OracleTypes;<br /><br />public class GetRssItems {<br /><br />public static void main(String[] args) throws Exception {<br />    DriverManager.registerDriver<br />            (new oracle.jdbc.driver.OracleDriver());<br />    Connection conn = DriverManager.getConnection(&quot;jdbc:oracle:thin:@localhost:1521:orcl&quot;    , &quot;up&quot;, &quot;up&quot;);<br />    String plsqlCall = &quot;BEGIN ? :=  get_rss_items; end;&quot;;<br />    OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall(plsqlCall);<br />    cstmt.registerOutParameter(1, OracleTypes.CURSOR);<br />    cstmt.execute();<br />    ResultSet rset = (ResultSet)cstmt.getObject(1);<br />    for(int i = 0;  rset.next(); i++ )<br />      System.out.println(  rset.getString(1)+ &quot; (on &quot; +rset.getString(3)+&quot;)&quot; );<br />  }<br />}<br />&nbsp;

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

The missing link is the function get_rss_items:

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

 

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:

...<br />    DriverManager.registerDriver<br />            (new oracle.jdbc.driver.OracleDriver());<br />    Connection conn = DriverManager.getConnection<br />                      ( &quot;jdbc:oracle:thin:@localhost:1521:orcl&quot;<br />                      , &quot;up&quot;, &quot;up&quot;);<br />    String plsqlCall = &quot;BEGIN &quot;<br />                     + &quot;  open ? for  select * &quot;<br />                     + &quot;              from   table( get_rss_items_tbl );&quot;<br />                     + &quot;END;&quot;;<br /><br />    OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall(plsqlCall);<br />    cstmt.registerOutParameter(1, OracleTypes.CURSOR);<br />    cstmt.execute();<br />    ResultSet rset = (ResultSet)cstmt.getObject(1);<br />    for(int i = 0;  rset.next(); i++ )<br />      System.out.println(  rset.getString(1)+ &quot; (on &quot; +rset.getString(3)+&quot;)&quot; );<br />  }<br />}<br /><br />
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

3 Comments

  1. http://technology.amis.nl/blog/?p=1176

    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;

    Did you actually test this? I tried to do exactly what you propose in PL/SQL and the Oracle compiler gives me the error messagses:
    PLS-00231 function ‘xxx’ may not be used in SQL

  2. Sukanya Subramanian on

    How do i manage if the Type has types inside ?
    For eg –

    CREATE OR REPLACE TYPE sample_type_internal AS OBJECT(
    phoneNumber number
    );

    CREATE OR REPLACE TYPE list_sample_type_internal IS TABLE OF sample_type_internal;

    CREATE OR REPLACE TYPE sample_type AS OBJECT(
    name VARCHAR2(20),
    listPhoneNumbers list_sample_type_internal
    );