Pass Record into Ref Cursor

Question

I have a stored procedure where I am populating a table from an array of records (Fees) and then putting those rows into a ref cursor.

TYPE rctl IS REF CURSOR ;
Fees   t_Fees;
type t_Fees is table of t_FeeRecord index by binary_integer;

type t_FeeRecord is record(
    description         varchar2(80),
    amount              number(12,2),
    taxAmount           number(12,2)
);

--populate the Fees array

INSERT into TEMPORARY_FEE(description,amount,tax) values(Fees(i).description,Fees(i).Amount,Fees(i).Tax);

OPEN rc1 FOR SELECT description,amount TEMPORARY_FEES;

This all works fine (populating the record, inserting into temp table and populating the ref cursor) but is it possible to eliminate the table and just pass my array of records directly into the ref_cursor? I have to return the results as a ref_cursor for a third party application.

I thought I might be able to try something like this.

OPEN rc1 FOR  
SELECT * FROM TABLE(cast(Fees as t_FeeRecord));

but I get an invalid datatype.

Not with the type defined in the package, as PL/SQL types can't be used in SQL (even if that SQL is itself inside PL/SQL). Your cast is also wrong, table() doesn't operate on record types. If t_fees was declared with create type at SQL level then you could just do select * from table(fees), though. Is that feasible? — Alex Poole

krokodilko krokodilko · Accepted Answer · 2013-10-05T22:31:21

Declare t_FeeRecord and t_Fees as database objects, not pl/sql objects,
in Oracle PL/SQL types cannot be used in SQL queries, this gives you datatype error (however, this restriction is removed in Oracle 12c).

t_FeeRecord must be created as object type, not a record type, since records are PL/SQL types and cannot be used in SQL queries.

create type t_FeeRecord is object(
    description         varchar2(80),
    amount              number(12,2),
    taxAmount           number(12,2)
);/

create type t_Fees as table of t_FeeRecord; /

Here is a simple demo that creates a table of records, open a ref cursor for this table and reads the cursor and inserts rows retrieved from the cursor to the table (tested on 11.2g):

create type t_FeeRecord is object(
    description         varchar2(80),
    amount              number(12,2),
    taxAmount           number(12,2)
);
/

create type t_Fees as table of t_FeeRecord; 
/

create table temporary_fee(
  description varchar2(80),
  amount      number(12,2),
  taxAmount   number(12,2)
);

declare
  fees t_Fees;
  TYPE rctl IS REF CURSOR;
  cur rctl;

  rec TEMPORARY_FEE%ROWTYPE;
begin 
  fees := t_Fees (
       t_FeeRecord( 'aaa', 20, 30 ),
       t_FeeRecord( 'bbb', 10, 76 ),
       t_FeeRecord( 'xxx', 4, 23 ),
       t_FeeRecord( 'zzz', 7, 43 ),
       t_FeeRecord( 'ccc', 13, 44 ) );

  open cur for
       select * from table( fees );

  LOOP
     FETCH cur INTO rec;
     EXIT WHEN cur%NOTFOUND;
     INSERT INTO TEMPORARY_FEE VALUES rec;
  END LOOP;
  close cur;
end;  
/

select * from temporary_fee;

DESCRIPTION      AMOUNT  TAXAMOUNT
------------ ---------- ----------
aaa                  20         30 
bbb                  10         76 
xxx                   4         23 
zzz                   7         43 
ccc                  13         44

Pass Record into Ref Cursor

1 Answers