PL SQL For Loop Sys_RefCursor

Question

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...?

I don't think so; but what's wrong with the fetch/while loop? — Alex Poole, Mar 09 '17 at 15:01
Do you absolutely need to use a ref cursor? IME, it's rare to need a ref cursor when working within PL/SQL - it's main usage would be to pass the cursor pointer out to non-PL/SQL programs so that they can loop through it as if they'd opened the cursor themselves. — Boneist, Mar 09 '17 at 15:15
Is the problem that you can't declare a record based on the weakly-typed ref cursor ([since %rowtype doesn't work](http://stackoverflow.com/q/11187376/266304)), either because you don't want to declare a record type or don't know what it will contain? The `x.y` reference suggests you know at least some fields though? — Alex Poole, Mar 09 '17 at 16:12
@Boneist Yep, I absolutely need to use a ref cursor. You've guessed right, there is an external program looping through that cursor, but part of larger series of events, I was trying to specifically look at the data returned from this cursor without having to run everything else. — 0909EM, Mar 10 '17 at 00:58
@AlexPoole ... I'm just after an elegant way to write the same piece of code. I've amended my question to show what a fetch/while might look like... it's just I'd have thought Oracle would help me here, with some syntactic sugar, if you like, to use a for loop. If it can't be done and I HAVE to use a fetch/while then so be it... — 0909EM, Mar 10 '17 at 01:10
If all you're wanting to do is test your procedure and see the contents of the ref cursor, you could use the SQL*Plus print method - see [this answer](http://stackoverflow.com/a/5822860) for how to use that. — Boneist, Mar 10 '17 at 07:28
Or maybe [this one](http://stackoverflow.com/a/27006514/266304) as you're testing a function; first two parts apply to SQL\*Plus or SQL Developer, the GUI part is obviously just the latter... — Alex Poole, Mar 10 '17 at 10:04

score 15 · Accepted Answer · edited Mar 10 '17 at 03:54

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;

A weak-ref cursor and a normal cursor may work differently but I'd have thought Oracle would provide some syntactic sugar to allow me to treat one as the other to be used in a for loop. That said, if it can't be done then that is the answer. — 0909EM, Mar 10 '17 at 01:17

PL SQL For Loop Sys_RefCursor

1 Answers1

Linked