Writing a Procedure for PL/SQL, SQL, and Java

Oracle 11g

Problem

• You need to write a PL/SQL function that returns a collection of records
• The function will be used by
  • other PL/SQL programs
  • SQL queries
  • a Java service

This can be a tough set of requirements to meet, especially when the records that you are returning don't map to a row from an existing table or view.

Solution

Write two flavors of the function: one that returns a nested table object and a second that calls the first function and returns a SYS_REFCURSOR.

Step 1: Create an object type that represents the record you will return.

CREATE OR REPLACE TYPE my_obj IS OBJECT
(
  obj_id           INTEGER,
  status           VARCHAR2(30),
  active           VARCHAR2(1)
)
/

Step 2: Create a nested table type based on your new object.

CREATE OR REPLACE TYPE my_obj_tab IS TABLE OF my_obj
/

Step 3: Create the package that contains the two flavors of the function.

CREATE OR REPLACE PACKAGE mypack IS

  FUNCTION f_get_objects RETURN my_obj_tab; -- use this for PL/SQL and SQL

  FUNCTION f_get_objects_cur RETURN SYS_REFCURSOR; -- use this one for Java

END mypack;
/
CREATE OR REPLACE PACKAGE BODY mypack IS

  FUNCTION f_get_objects RETURN my_obj_tab IS
  BEGIN
    RETURN my_obj_tab(my_obj(obj_id => 1,
                             status => 'whatever',
                             active => 'Y'),
                      my_obj(obj_id => 2,
                             status => 'whatever again',
                             active => 'Y'));
  END f_get_objects;


  FUNCTION f_get_objects_cur RETURN SYS_REFCURSOR IS
    l_cursor SYS_REFCURSOR;
  BEGIN
    OPEN l_cursor FOR
      SELECT *
      FROM   TABLE(f_get_objects);
    RETURN l_cursor;
  END f_get_objects_cur;

END mypack;
/

Using the function in PL/SQL

DECLARE
  l_objects my_obj_tab;
BEGIN
  l_objects := mypack.f_get_objects;
  dbms_output.put_line(l_objects(1).status);
END
/

whatever
 
PL/SQL procedure successfully completed

Using the function in SQL

SELECT *
FROM   TABLE(mypack.f_get_objects)
ORDER  BY status;

Using the function in JAVA

...
OracleCallableStatement cs = (OracleCallableStatement)
  conn.prepareCall("{? = call mypack.f_get_objects_cur() }"); //no params
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
OracleResultSet rs = (OracleResultSet) cs.getCursor(1);
while (rs.next()){
 System.out.println("ID:" + rs.getInt("obj_id") + " STATUS:"
   + rs.getString("status"));
}
...