0 
0 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)+")" );
}
}
Related posts:
JavaOne 2008: OpenSSO – creating federated relationships for secure SaaS, Social Networking and Web 2.0 
Accepted AMIS Oracle Open World Presentations 
Java: Generating PDF and Previewing it as an Image – iText and PDF Renderer Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond 
The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
Hi
This might be a little off the subject but having worked with
pl/sql and ref cursors ( of which I am really hoping will be
obsolete in the future ) I can recommend this article
http://www.oracle-base.com/articles/8i/ComplexRecordsets.php#PLSQLTables
for working with in memory pl/sql types and refcursors
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
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
);