cur_variable_passing2.sql

/*  The following example is passing CURSOR VARIABLE betweeb PL/SQL and using CURSOR VARIABLE
 *  to return a RECORD Type.
 */


Create or replace Package Emp_Pck IS
Type emp_rec IS RECORD (a1 varchar2(10),
                        a2 varchar2(10));
Type emp_cur IS REF CURSOR return emp_rec;
       Procedure Open_Cur ( C1 IN OUT emp_cur);
       
END;
/
Create or replace Package body Emp_Pck
IS
Procedure Open_Cur ( C1 IN OUT emp_cur)
IS
BEGIN
         OPEN C1 for Select a1, a2 from a;
END;
       
END;
/



declare

test emp_pck.emp_cur;
aa emp_pck.emp_rec;


begin
 emp_pck.open_cur(test);
 
 fetch test INTO aa;
 
 while test%found loop 
  insert into a values (aa.a1, aa.a2);
  
  
 fetch test INTO aa;
  end loop;
  
  
 
end;
/