Printing Oracle Sys_refcursor in Oracle SQL Developer 1.5

Question

I am trying to execute the procedure which returns a sys_refcursor as output. The procedure is PROCEDURE GET_EMPLOYEEs(P_ID in NUMBER, P_OUT_CURSOR OUT SYS_REFCURSOR);

I wrote the below anonymous block in SQL Developer 1.5 and its executing fine,but when I try to print the cursor, I am getting an error. The cursor returns emp_name,salary and other columns.

set serveroutput on;
declare
result sys_refcursor;
begin
emp.emp360_utils.GET_EMPLOYEEs(222334,result); 
dbms_output.put_line(result); // Error here
end;

The error is

PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

UPDATED: Iterated for cursor,but still getting error as "Invalid reference to variable dummycursor".

    set serveroutput on;
    declare
    dummycursor sys_refcursor;
    result sys_refcursor;
    begin
     emp.emp360_utils.GET_EMPLOYEEs(222334,result); 
    LOOP
    fetch result into dummycursor;
    EXIT when result%notfound;
    dbms_output.putline(dummycursor.lsn);
    end loop;
    end;

Solution 1

You would need to loop over the ref cursor and for each row in it, print out the individual fields. In your updated version you need to fetch the cursor into local scalar variables, not another ref cursor:

set serveroutput on;
declare
  result sys_refcursor;
  lsn number; -- guessing the data type
begin
  emp.emp360_utils.GET_EMPLOYEEs(222334,result); 
  loop
    fetch result into lsn; -- and other columns if needed
    exit when result%notfound;
    dbms_output.put_line(lsn);
  end loop;
end;
/

I've guessed lsn is a number, if not then declare that as the right type. If the cursor returns more than one column then you will need to declare local variables for each of them and fetch them all into those, even if you're only displaying one of them.

---

If you just want to display it then you can use a bind variable to do this instead (checked in the current version and back to 1.5.0):

variable result refcursor

begin
  emp.emp360_utils.GET_EMPLOYEEs(222334, :result); 
end;
/

print result

Note that the variable command is not in the declare block; it is a SQL Developer command, not a PL/SQL command. As is print, though both are only documented in the SQL*Plus docs. And also note the colon at the start of :result within the block, which indicates that it is a bind variable, not a local PL/SQL variable.

Solution 2

You can execute procedure using Run button in package source

and view cursor content in tab Output variables

Comments

[Sanjana]

I am trying to iterate over the cursor, but getting the error.Code updated.

[Alex Poole]

@Sanjana updated; you need to fetch each column from the ref cursor into its own local variable, not the whole thing into a new ref cursor.