-
Okay folks, here's the situation:
I have a script that based on a certain criteria (gotten at runtime) will perform many operations on one of 2 cursors. Now, the operations to be done will be the same either way.
So instead of:
cursor x is ....
cursor y is ....
Begin
if flag = 0 then
for i in x loop
...
...
...
end loop;
elsif flag = 1 then
for j in y loop
...
...
...
end loop;
end if;
I want to do just:
cursor x is ....
cursor y is ....
cursor_name
Begin
if flag = 0 then
cursor_name := 'x';
elsif flag = 1 then
cursor_name := 'y';
end if;
for i in cursor_name loop
...
...
...
end loop;
End;
Now, I tried this and it didnt work, is there anyway to make it work?
(I dont want to create a procedure for what is inside the for loops, i just want to know if cursor_name can be dynamic like that)
thanx in advance
Peace,
Sakitah
-
hi
u can do it using a dynamic pl/sql block. for example,
declare
cursor c1 is
select count(*) cnt from emp;
cursor c2 is
select count(*) cnt from dept;
j number:=1;
x varchar2(200);
plsql varchar2(200);
begin
if j=0 then
x:='c1';
else
x:='c2';
end if;
plsql := 'declare
cursor c1 is
select count(*) cnt from emp;
cursor c2 is
select count(*) cnt from dept;
begin
for i in ' || x || chr(10) ||
'loop
dbms_output.put_line(''cnt gen. >>> ''||i.cnt);
end loop;
end;';
execute immediate plsql;
end;
/
Cheers!
OraKid.
-
hi ,
sorry forgot to remove the DECLARE CURSOR stmt @ declaration part.
declare
j number:=0;
x varchar2(200);
plsql varchar2(200);
begin
if j=0 then
x:='c1';
else
x:='c2';
end if;
plsql := 'declare
cursor c1 is
select count(*) cnt from emp;
cursor c2 is
select count(*) cnt from dept;
begin
for i in ' || x || chr(10) ||
'loop
dbms_output.put_line(''cnt gen. >>> ''||i.cnt);
end loop;
end;';
execute immediate plsql;
end;
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|