| Assigning cursor to cursor? [message #314941] |
Fri, 18 April 2008 06:03  |
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 #314947 is a reply to message #314941] |
Fri, 18 April 2008 06:26  |
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 |
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 |
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;
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
