Description Mike Norman 2011-03-14 10:21:48 EDT

An example of a strongly-typed package REF CURSOR:

CREATE OR REPLACE PACKAGE PKG_STRONG_REFCURSOR IS

    TYPE PRICE_RECORD IS RECORD (
        INDEX_RATE NUMBER(6,5),
        INDEX_TYPE VARCHAR2(1)
    );
    TYPE STRONG_REFCURSOR IS REF CURSOR RETURN PRICE_RECORD;

    PROCEDURE FOO(P_RETURN OUT STRONG_REFCURSOR);

END PKG_STRONG_REFCURSOR;

When building the anonymous PL/SQL block, a new nested function is
required to convert the PRICE_RECORD PL/SQL records to a compatible
JDBC Object type:

CREATE OR REPLACE TYPE t_price_rec AS OBJECT (
  INDEX_RATE NUMBER(6,5),
  INDEX_TYPE VARCHAR2(1)
);
CREATE OR REPLACE TYPE t_price_rec_tab AS TABLE OF t_price_rec;


FUNCTION pl2el5(C IN STRONG_REFCURSOR) RETURN t_price_rec_tab
 PIPELINED IS
   TYPE l_cur_type IS REF CURSOR;
   l_cur l_cur_type;
   l_price_rec price_record;
 BEGIN
   OPEN l_cur FOR C;
   LOOP
     FETCH l_cur
     INTO l_price_rec;
     EXIT WHEN l_cur%NOTFOUND;
     PIPE ROW(t_price_rec(
         index_rate => l_rec.index_rate,
         index_type => l_rec.index_type));
 END LOOP;
 RETURN;
END;

Or something like it - more research is required