pms_reddy1
asked on
Ref Cursors
What is the main use of Ref cursors? when should I go for it(With no other alternative)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
When should we use Ref & when should we use collection types in terms of memory/performance, Could you please provide some examples.
Thanks,
Unfortunately this is a different question.
The quick answer is basically what I posted in your question about collections:
https://www.experts-exchange.com/questions/27673026/Collections.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
The quick answer is basically what I posted in your question about collections:
https://www.experts-exchange.com/questions/27673026/Collections.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
ASKER
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,
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,
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have done some more research on this topic and able to find some important points which I did not find the given answer.
ASKER
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,
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,
ASKER
Please close the request.
Please review:
https://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.