CODEWITHSUNDEEP
oracle-databaseplsql
EvilEddie
EvilEddie

Reputation: 1035

Pass Record into Ref Cursor

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.

Upvotes: 1

Views: 6618

Answers (1)

krokodilko
krokodilko

Reputation: 36127

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

Upvotes: 1

Related Questions