Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dereferencing a REF CURSOR in PL/SQL

Posted on 2001-09-11
14
Medium Priority
?
737 Views
Last Modified: 2012-05-04
I have a stored Proc that returns a REF CUROSR (Pointer).
In VB using ADO 2.5 I can happily assign this to a recordset and look thru what I have.
How though can I do this Server side in PL/SQL.
It sounds like it should be very easy.
One answer is to use a a parameterized cursor rather than a stored proc but I wish to avoid this if possible.

Thanks
Aaron
0
Comment
Question by:possum69
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 4

Expert Comment

by:jtrifts
ID: 6473992
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;

0
 

Expert Comment

by:Kirilloff
ID: 6474270
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.
0
 

Author Comment

by:possum69
ID: 6475484
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!
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 1

Expert Comment

by:Moondancer
ID: 7034776
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.
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 7036632
I'll take the points.  

Kirilloff's response is missing:

"open c1;"

statement and will not work.
Regards,
JT
0
 

Author Comment

by:possum69
ID: 7036654
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!
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 7036658
...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
0
 

Author Comment

by:possum69
ID: 7036664
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?
0
 
LVL 4

Accepted Solution

by:
jtrifts earned 800 total points
ID: 7036670
There are some who would prefer not to have a response accepted with anything less than an "A".  

Personally I don't care one way or another.  I do like to get the points, but for me, that's not really the point of E-E.  I like to help, and I like to learn.

In any case, I think the "quality" should be judged as you see fit.  For E-E to have long term viability, the Questioners need to receive satisfactory answers...afterall, you're the "client".

¦¬)

Regards,
JT
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7037344
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
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 7047034
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
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7052396
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
0
 

Expert Comment

by:simonredfern
ID: 7714261
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.
0
 

Expert Comment

by:simonredfern
ID: 7714268
sorry, the procedure uses the view v_users
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question