No question at this time
DBA Top 10
1 A. Kavsek 8600
2 M. Cadot 7000
3 B. Vroman 6000
4 T. Boles 4550
5 J. Schnackenberg 4100
6 P. Wisse 3000
7 T. P 1200
8 G. Lambregts 1100
8 . Lauri 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48373
Total active users1542
Act. users last 24h9
Act. users last hour0
Registered user hits last week188
Registered user hits last month1051
Go up

For-loop with dynamic SQL
Next thread: Packages in memory
Prev thread: Update privileges

Message Score Author Date
Hi, I am working with Oracle RDBMS 12c. I ha...... Lauri Feb 19, 2019, 16:13
Explained in https://blogs.oracle.com/oraclemagaz...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Rob Pattyn Feb 19, 2019, 16:37
declare v_table varchar2(30) := 'EMP'; v_sq...... David Johnson Feb 19, 2019, 17:23
Hi, Thanks. It has been a long time I wrote PL/...... Lauri Feb 20, 2019, 11:38

Follow up by mail Click here


Subject: For-loop with dynamic SQL
Author: Lauri, Netherlands
Date: Feb 19, 2019, 16:13, 91 days ago
Os info: all
Oracle info: 12c and higher
Message: Hi,

I am working with Oracle RDBMS 12c.

I have a varriable that holds an SQL code, for instance:

v_sql varchar2(3000) := ' select empno, ename from ' || v_table || ' where hiredate < sysdate - 1000' ;

How can I construct a for-loop to use the above query, like:

...
for c in v_sql
loop
...do something...
end loop;

Thanks by adance.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: For-loop with dynamic SQL
Author: Rob Pattyn, Belgium
Date: Feb 19, 2019, 16:37, 91 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Explained in
https://blogs.oracle.com/oraclemagazine/working-with-cursors
part of
https://www.oracle.com/database/technologies/appdev/plsql.html
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: For-loop with dynamic SQL
Author: David Johnson, United States
Date: Feb 19, 2019, 17:23, 91 days ago
Message: declare
v_table varchar2(30) := 'EMP';
v_sql varchar2(3000) := ' select empno, ename from '
|| v_table || ' where hiredate < sysdate - 1000';
sqltext varchar2(32000) :=
'declare
myqry varchar2(3000) := '''||v_sql||''';'
begin
for c in myqry loop
...dosomething ;
end loop;
end;
'
begin
execute immediate sqltext;
end;
/


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: For-loop with dynamic SQL
Author: Lauri, Netherlands
Date: Feb 20, 2019, 11:38, 90 days ago
Message: Hi,

Thanks. It has been a long time I wrote PL/SQL for developments... I should have known this :-(
I have found also that very interesting link:
http://www.adp-gmbh.ch/ora/plsql/cursors/dynamic.html

Kind Regards,

Lauri
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here