Home » SQL & PL/SQL » SQL & PL/SQL » Pass Variable to cursor (oracle 10g)
Pass Variable to cursor [message #413220] Tue, 14 July 2009 16:47 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
hi frirnds,
need your help.

create or replace procedure proc_1
v_s1 varchhar2;
v_s2 varchhar2;
cursor c1(v_s1 char ,v_s2 char) is
select * from emp order by v_s1,v_s2;

Begin

v_s1 :='empno ASC';
v_s2 :='empname DESC';


for r_c1 in c1(v_s1, v_s2) loop

insert into tab2 (r_c1.empno);
end loop;

End;

I am facing problem in
How to execute the cursor. I am expecting the cursor should fetch the data like
select * from emp order by empno ASC,empname DESC;


Re: Pass Variable to cursor [message #413222 is a reply to message #413220] Tue, 14 July 2009 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am facing problem in
>How to execute the cursor.
I disagree slightly.

While you may have a challenge to execute the cursor,
you have a more pressing problem in writing a procedure with valid syntax.

Please try again.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Pass Variable to cursor [message #413226 is a reply to message #413220] Tue, 14 July 2009 19:06 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well to have a dynamic order by you'd need to use dynamic sql.

However in your example you're inserting the data from the cursor into another table. Rows in tables have no inherent order so any order by in the cursor in your example is pointless.
Re: Pass Variable to cursor [message #413234 is a reply to message #413226] Tue, 14 July 2009 22:51 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Thanks cookiemonster,

But my problem is how can i put the dynamic sql in the cursor varia ble.
Re: Pass Variable to cursor [message #413235 is a reply to message #413220] Tue, 14 July 2009 22:54 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SEARCH this forum for "dynamic SQL"!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Previous Topic: Sum With Select problem (merged 4)
Next Topic: Exam 1z0-147 and autonomous transactions in triggers
Goto Forum:
  


Current Time: Fri Dec 09 17:35:52 CST 2016

Total time taken to generate the page: 0.29233 seconds