Solved

Dereferencing a REF CURSOR in PL/SQL

Posted on 2001-09-11
14
719 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 200 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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 …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

691 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