Search code examples
oracleplsqlrecordref-cursor

How to use record to loop a ref cursor?


I want to write PL/SQL to test a function in a package. The package defines a cursor type

TYPE ref_cursor IS REF CURSOR;

I want to define a record based on that type.

My code is:

DECLARE
  cur PACKAGE_NAME.ref_cursor; 
  rec cur%ROWTYPE;

why is last line not correct?


Solution

  • 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_refcursor
    is
      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;