Oracle stored procedure – Loop within cursor

cursororacle10gplsqlstored-procedures

I have a stored proc Procedure1,

  1. Within Procedure1, I am calling another procedure Procedure2 which returns sys_refcursor ProcResult2.
  2. 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.

Best Answer

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;
Related Topic