CODEWITHSUNDEEP
sqloracle-databaseplsql
giorge
giorge

Reputation: 33

How to return a cursor populated with NESTED RECORD type from Oracle procedure

I need to return a cursor from Oracle procedure, the cursor has to contain RECORD types. I know how to solve this problem when the RECORD is simple, but how can this be solved for NESTED RECORDS?

There is a working block of code for simple RECORD: 

-- create package with a RECORD type
create or replace package pkg as
    -- a record contains only one simple attribute
    type t_rec is RECORD (
        simple_attr number
    );
end;
/

-- create a testing procedure
-- it returns a cursor populated with pkg.t_rec records
create or replace procedure test_it(ret OUT SYS_REFCURSOR) is
    type cur_t is ref cursor return pkg.t_rec;
    cur cur_t;
begin
    -- this is critical; it is easy to populate simple RECORD type,
    -- because select result is mapped transparently to the RECORD elements
    open cur for select 1 from dual;
    ret := cur;  -- assign the cursor to the OUT parameter
end;
/

-- and now test it
-- it will print one number (1) to the output
declare
    refcur SYS_REFCURSOR;
    r pkg.t_rec;
begin
    -- call a procedure to initialize cursor
    test_it(refcur);
    -- print out cursor elements
    loop
        fetch refcur into r;
        exit when refcur%notfound; 
        dbms_output.put_line(r.simple_attr);
    end loop;  
    close refcur;
end;
/

Can you show me, how it could be done when a RECORD t_rec contains NESTED RECORD?

Modify the example in the folowing way:

-- create package with a NESTED RECORD type
create or replace package pkg as
    type t_rec_nested is RECORD (
        nested_attr number
    );
    -- a record with NESTED RECORD 
    type t_rec is RECORD (
        simple_attr number,
        nested_rec t_rec_nested
    );
end;
/

create or replace procedure test_it(ret OUT SYS_REFCURSOR) is
    type cur_t is ref cursor return pkg.t_rec;
    cur cur_t;
begin
    -- how to populate a result?
    open cur for ????
    ret := cur;
end;
/

The question is how to modify test_it procedure to populate a cursor? I spent many hours searching the solution, I will appreciate any help.

Upvotes: 3

Views: 4111

Answers (1)

Greg Reynolds
Greg Reynolds

Reputation: 10186

I don't think it's possible as you have it, as RECORDs are a PL/SQL data type. You can do an equivalent thing by making an OBJECT. If you scroll down to the bottom of this link (or search for "Updating a Row Using a Record Containing an Object: Example" on the page), you will see how that is handled.

Upvotes: 2

Related Questions