How can I explore an out cursor parameter in a PLSQL block?

Question

I need to verify a procedure that has an out cursor parameter. Specifically I need to see what is retrieving.

I try this:

declare
  type cursor_out is ref cursor;
  cur cursor_out; 
  fila activ_economica%rowtype;
  procedure test(algo out cursor_out) is
  begin
    open algo for select * from activ_economica;  
  end;
begin
  test(cur);
  for i in cur loop
    fila := i;
    dbms_output.put(fila.id_activ_economica ||' ' );
    dbms_output.put_line(fila.nom_activ_economica);
  end loop;
end;

The error is that "cur" has not being defined.

Answer 1

You cannot use cursor FOR loop with a ref cursor, you must do this:

declare
  type cursor_out is ref cursor;
  cur cursor_out; 
  fila activ_economica%rowtype;
  procedure test(algo out cursor_out) is
  begin
    open algo for select * from activ_economica;  
  end;
begin
  test(cur);
  loop
    fetch cur into fila;
    exit when cur%notfound;
    dbms_output.put(fila.id_activ_economica ||' ' );
    dbms_output.put_line(fila.nom_activ_economica);
  end loop;
  close cur;
end;

Note: there is no longer any need to define your own ref cursor type (unless you are on a very old version of Oracle). You can just use SYS_REFCURSOR instead:

declare
  cur sys_refcursor; 
  ...