Dereferencing a REF CURSOR in PL/SQL

A ref cursor in pl/sql is essentially a PL/SQL table of records. To loop through, you CAN NOT treat it as a normal Oracle table.  However there are constructs to do so.

Consider if your pl/sql table is called "tab1" and has two *fields* called "col1" and "col2".

The table is examined through normal dot notation:
tablename(recordcounter).columnname
e.g.
tab1(i).col1
or
tab1(10).col2
etcetera...

You can loop through as follows:

i:= 0;
LOOP
  i:= i + 1;
  DBMS_OUTPUT.PUT_LINE(tab1(i).col1);
  DBMS_OUTPUT.PUT_LINE(tab1(i).col2);
END LOOP;

You have to explicitly fetch the cursor. Package PACK contains definition of cur as TYPE CUR IS REF CURSOR; and procedure PROC returns a cursor with three fields. You can write

declare c pack.cur;
   n1 number;
   n2 varchar2(20);
   n varchar2(60);
begin
  pack.proc(c);
  loop
    fetch c into n1,n2,n;
    exit when c%notfound;
    dbms_output.put_line(n);
  end loop;
end;

Notice that you cannot use cursor FOR loop with ref cursor variables and you must declare as many variables as fields in the cursor even if you do not use them all.

What about when you don't know how many columns are in the REF CURSOR i.e, Loosely Bound rather Tightly bound.

Also when this is so easy in the SQLPlus sheel why is it so hard in PL/SQL.



SQLPlus
Where test1 is a stotred proc with an out paramter of type
REF CURSOR

> variable x refcursor
> exec test1(:x)
> print x;

Voila

And when you set autoprint on even the last step is not needed!

Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Please click your Member Profile, expand View Question History to navigate and update all your open and locked questions.

I'll take the points.  

Kirilloff's response is missing:

"open c1;"

statement and will not work.
Regards,
JT

This Part of the question is still not answered.

What about when you don't know how many columns are in the REF CURSOR i.e, Loosely Bound rather Tightly
bound.

Also when this is so easy in the SQLPlus sheel why is it so hard in PL/SQL.
This part of the question is still not answered


SQLPlus
Where test1 is a stotred proc with an out paramter of type
REF CURSOR

> variable x refcursor
> exec test1(:x)
> print x;

Voila

And when you set autoprint on even the last step is not needed!

...true...but isn't that an additional question? (It does not appear in your original Q.)

But...If you're not happy with any of the comments, then perhaps you should ask the mod to delete the question altogether.

Regards,
JT

Good Point.
Happy To accept the Comment as an Answer with an "Acceptable" Grade But don't want to give offense by giving such a grade if it would do so?

Please clarify something for me here.  You gave a "C" grade, the lowest grade here; few appreciate it and as I've read this question numerous times, the initial question was indeed answered in "A" level mode.  Would you like me to correct this?

Listening further when I can.

Moondancer - EE Moderator

I think he provided C because he later asked an additional question that went unanswered. (a bit cheeky...but I'm not terrifically bothered).
JT

Grade changed to A.
1) No response as to why a "C" was given
2) Few will access this question in our PAQ at a "C" level answer, although this could help someone else
3) It helps your grading history for experts in the future, possum69, since many will not help those who have poor grades reflected in their history.
Thanks,
Moondancer - EE Moderator

if your ref cursor has a variable where clause but a fixed select statement thats a select * from table or select * from view, you can use the following approach:

declare
TYPE ref_cur IS REF CURSOR;

users_cursor ref_cur;

-- the view used in the procedure below.
users_rec v_users%rowtype;

begin

-- this procedure returns a ref cursor (select * from users where....)
pa_main.pr_login_user('user','pass',users_cursor);


loop
   fetch users_cursor into users_rec;
   -- process
   exit when users_cursor%notfound;
end loop;


end;

cheers,

Simon.

sorry, the procedure uses the view v_users