I'm using Oracle 12c. In PL/SQL I can do this
set serveroutput on
declare
begin
for x in (select 1 as y from dual) loop
dbms_output.put_line(x.y);
end loop;
end;
I can also do this...
set serveroutput on
declare
cursor c1 is
select 1 as y from dual;
begin
for x in c1 loop
dbms_output.put_line(x.y);
end loop;
end;
So far, so good. But can I do this with a sys_refcursor? I am aware I could do it with a fetch/while loop but prefer the for loop syntax (I think it is a lot cleaner)...
set serveroutput on
declare
cur sys_refcursor;
begin
cur := Package.GetData(1234);
fetch cur into y;
while cur%FOUND loop
dbms_output.put_line(y);
fetch cur into y;
end loop;
end;
I want to do...
set serveroutput on
declare
cur sys_refcursor;
begin
cur := PACKAGE.GetData(1234); -- This returns a sys_refcursor
for x in cur loop
dbms_output.put_line(x.y);
end loop;
end;
Error report -
ORA-06550: line 5, column 16:
PLS-00221: 'cur' is not a procedure or is undefined
Is there a mechanism to for loop through the sys_refcursor (rather than the fetch into/while loop)? Perhaps something new-fangled in 12c that I don't know about...?
Answer
SYS_REFCURSOR is merely a pre-declared weak ref cursor. There is no such mechanism to loop through sys_refcursor without fetching. Also you cannot compare a weak refcursor with normal cursor and they work differently. It's a buffer space which is allocated to hold the result temporarily. When you run the below statement in PLSQL block, PLSQL engine doesnot understand it a PLSQL variable and throws the error
for x in cur loop
PLS-00221: 'CUR' is not a procedure or is undefined
Apart the below statement will also fail since you didnot defined the OUT paramater to the Package if its retruning a SYS_REFCURSOR.
cur := PACKAGE.GetData(1234);
You can fetch the content of the SYS_REFCURSOR and then display it as below:
declare
a SYS_REFCURSOR;
v_emp_id employee.emp_id%type;
begin
--- This is a procedure with OUT parameter as SYS_REFCURSOR
dynmc_selec(emp_output=>a);
loop
FETCH a INTO v_emp_id;
EXIT WHEN a%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_id );
end loop;
end;