How to use record to loop a ref cursor?
You can't define a record type based on a weakly-typed REF CURSOR. Since the cursor type defined in the package can be used to return data from an arbitrary query with arbitrary columns, the PL/SQL compiler can't determine an appropriate record type to fetch the data into.
If you know the actual data being returned from the function, you could declare a record of that type to fetch the data into. For example, if I declare a function that returns a weakly-typed cursor type but I know that the cursor really returns a cursor based on the EMP
table, I can fetch the data into an EMP%ROWTYPE
record (note that SYS_REFCURSOR
is a system-defined weakly-typed REF CURSOR type)
create or replace function f1 return sys_refcursoris l_rc sys_refcursor;begin open l_rc for select * from emp; return l_rc;end;declare l_rc sys_refcursor; l_emp emp%rowtype;begin l_rc := f1; loop fetch l_rc into l_emp; exit when l_rc%notfound; dbms_output.put_line( l_emp.empno ); end loop;end;