Home » SQL & PL/SQL » SQL & PL/SQL » Assigning cursor to cursor? (Oracle, Windows XP)
Assigning cursor to cursor? [message #314941] Fri, 18 April 2008 06:03 Go to next message
AAMordenAA
Messages: 3
Registered: April 2008
Junior Member
PL/SQL:
I have a procedure that has to return a cursor,
but i have to different select statements for this cursor depending on the
content of a varchar2 parameter of the procedure.

My thought was to declare both cursors with the two different select statements,
then use if statements to check the varchar2 parameter and assign the correct
cursor to a third cursor, which I already set as an out parameter to the procedure.
But I am unable to assign one cursor to another, is there a way to do this, or possibly another solution to my problem?
Re: Assigning cursor to cursor? [message #314946 is a reply to message #314941] Fri, 18 April 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use REF CURSOR variables.

Regards
Michel
Re: Assigning cursor to cursor? [message #314947 is a reply to message #314941] Fri, 18 April 2008 06:26 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
One option to your problem would go along the lines of:

if a then
open your_refcursor_output_param for select blah from a;
else
open your_refcursor_output_param for select blah from b;
end if;

Check out the PLSQL user guide for more details (chapter 6 on 10g).
Re: Assigning cursor to cursor? [message #314948 is a reply to message #314941] Fri, 18 April 2008 06:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Spend some time in reading pl/sql reference manual and you will understand how to do it and which is the best way. To start with search for the keyword ref cursor.

Regards

Raj
Re: Assigning cursor to cursor? [message #314951 is a reply to message #314947] Fri, 18 April 2008 06:32 Go to previous messageGo to next message
AAMordenAA
Messages: 3
Registered: April 2008
Junior Member
I've been experimenting with ref cursor, and it seems to me that I cant select from more than one table using that, is this correct?
My select statement:
SELECT råvare.råvare_nr, råvaresilo.beholdning, sum((prod_plan.kvantum/100)*reseptlinje.prosent_andel) as Behov, (råvaresilo.beholdning - (sum((prod_plan.kvantum/100)*reseptlinje.prosent_andel))) as Rest FROM ferdigvare, resept, reseptlinje, råvare, råvaresilo, prod_plan
WHERE
prod_plan.ferdigvare_nr = ferdigvare.ferdigvare_nr AND
ferdigvare.ferdigvare_nr = resept.ferdigvare_nr AND
resept.resept_nr = reseptlinje.resept_nr AND
reseptlinje.råvare_nr = råvare.råvare_nr AND
råvare.råvare_nr = råvaresilo.råvare_nr AND
reseptlinje.revisjons_nr= '1' AND
prod_plan.forslag ='0' AND
ferdigvare.ferdigvare_nr IN(Select ferdigvare_nr FROM prod_plan WHERE ProdDato BETWEEN fradato AND tildato)
GROUP BY råvare.råvare_nr, råvaresilo.beholdning
ORDER BY råvare.råvare_nr;
Re: Assigning cursor to cursor? [message #314957 is a reply to message #314951] Fri, 18 April 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've been experimenting with ref cursor,

But you didn't experiment reading OraFAQ Forum Guide to know how to format your post.

Quote:
it seems to me that I cant select from more than one table using that, is this correct?

No. You can select what you want.

Regards
Michel
Re: Assigning cursor to cursor? [message #314960 is a reply to message #314957] Fri, 18 April 2008 06:46 Go to previous message
AAMordenAA
Messages: 3
Registered: April 2008
Junior Member
ok, thanks for the reply.
Previous Topic: Concatenation and Insertion
Next Topic: How to aviod usage of cursor and improve performance
Goto Forum:
  


Current Time: Sun Dec 11 04:04:33 CST 2016

Total time taken to generate the page: 0.12932 seconds