Oracle stored procedure - Loop within cursor

Question

I have a stored proc Procedure1,

Within Procedure1, I am calling another procedure Procedure2 which returns sys_refcursor ProcResult2.
I need to traverse through each row of this cursor and insert the data into a table

The code

Create Procedure1()  
as   
ProcResult1 Sys_refcursor;  
begin  
    Procedure2(ProcResult2);  
    For eachrow in ProcResult2  
      Loop  
        insert into test_table(ProcResult2.Id);  
      end loop;  
end ;\

Any idea, pointers on how to achieve the 2. functionality ? That is traverse through each row returned in ProcResult2 and insert into a new table.

Tony Andrews · Accepted Answer · 2012-05-04 10:04:43Z

2

You are almost there:

Create Procedure1()  
as   
    ProcResult1 Sys_refcursor;  
    v_record ???; -- See below
begin  
    Procedure2(ProcResult2);  
    Loop  
        fetch ProcResult2 into v_record;
        exit when ProcResult2%notfound;
        insert into test_table(v_record.Id);  
    end loop;  
    close ProcResult2;
end;

You need to declare the variable v_record with a structure that matches what the cursor returns - i.e. you need to know the structure of the cursor record when you build the procedure. If the cursor returns "select * from mytab" then you could use:

v_record mytab%rowtype;

But if it only selects certain columns e.g. "select col1, col3 from mytab" then you would need to manually define the structure:

type v_record_type is record (col1 number, col3 varchar2(10));
v_record v_record_type;

answered May 4, 2012 at 10:04

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle stored procedure - Loop within cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related