Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ref Cursors

Posted on 2012-04-12
16
Medium Priority
?
536 Views
Last Modified: 2012-04-13
What is the main use of Ref cursors? when should I go for it(With no other alternative)?
0
Comment
Question by:pms_reddy1
[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
  • 6
  • 5
16 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 501 total points
ID: 37838124
http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

A ref_cursor is a reference to a cursor in a stored procedure or function.  Essentialy it is used as a parameter type when you need to pass the cursor around.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 999 total points
ID: 37838167
In addition to the above:

A cursor is nothing more than a result-set.  Every select you run creates a cursor to present the results.

Alternatives are anything that is a 'collection'.  In Oracle you could use a PL/SQL table (not a normal databsae table) or a VARRAY:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CIHIEBJC

The difference with a cursor over the other collection types is it is one-way one-time processing.  You move through the records forward only and only once.  You re-process the results, you would need to basically reopen the cursor by reexecuting the select statement that generated it.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37842153
Might I ask why this was given a C grade?  If you were in need of additional information you should ask.

Please review:
http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26

If you do not respond, I'll ask a Moderator to review the grade on this question.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:pms_reddy1
ID: 37842410
Hi slightwv,

When should we use Ref & when should we use collection types in terms of memory/performance, Could you please provide some examples.

Thanks,
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37842463
Unfortunately this is a different question.

The quick answer is basically what I posted in your question about collections:
http://www.experts-exchange.com/Database/Oracle/Q_27673026.html

Use the proper tool for the proper job.  Cursors are probably the easiest to pass around result sets between processes.  As long as you do not need the results to persist.

If you need the results to persist or need to move through them more than once or backwards, then a collection is likely the best.

As far as examples go, I'm not sure what you are looking for that isn't availabe in the docs that talk about the different types.

A simple cursor example using sqlplus is:


var mycur refcursor

begin
open :mycur for 'select sysdate from dual';
end;
/

print mycur
0
 

Author Comment

by:pms_reddy1
ID: 37842873
In my main question I asked "when should I go for it(With no other alternative)?"
I did not find the answer for my question in your replay, Insted you mentioned the alternative as collections.
Hi slightwv,

If you understand my question properly ,I am asking is there any situvation like "we have to opt for the ref cursors, No other way" , Atleast you did not read my question properly, That's why I have given the grade as C.

Please respond If you know the answer,Don't look for the points/Grade, If the reader satisfy with your answer obviously you will get the points/Grade A, No need to demand.

Thanks,
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37842988
99% of the time there are always alternatives.  You use cursors when they make sense to use.  You use collections when they make sense.

The only time you do not have an option is when you issue a select.  A cursor is built for you behind the scenes.

For example: select sysdate from dual;

The results presented to you are pulled from a cursor that you really cannot control.
0
 

Author Comment

by:pms_reddy1
ID: 37843252
REF CURSORS will always be recognized by the client program, whatever the language ..
 whereas you may have bad surprises with collections support.
 If "collection" is implemented, hence you will need lot of bandwidth between server & client to pass all the data from server to client.
 using collections could be much worse in terms of memory consumption.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 999 total points
ID: 37843277
OK, I found the two web references where you copied and pasted that from.  Is that supposed to mean something to me?

They are also, opinions...

I stand by my comments:  Use the right tool for the right job.

You cannot say: Give me the 12th row from a cursor.  You can from a collection.
0
 

Author Comment

by:pms_reddy1
ID: 37843296
I have done some more research on this topic and able to find some important points  which I did not find the given answer.
0
 

Author Comment

by:pms_reddy1
ID: 37843453
Hi slightwv,
I got some information from some sites and thought of providing for the users knowledge.
Infact it is not something for you,
Atleast I am reading some links and gathering some information,where as you are not reading the questions properly and pasting the related links for the sake of points&grades.

anyway Thanks for your time & efforts,
0
 

Author Comment

by:pms_reddy1
ID: 37843457
Please close the request.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

722 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