No question at this time
DBA Top 10
1 M. Cadot 9900
2 B. Vroman 9200
3 A. Kavsek 7500
4 T. Boles 6250
5 J. Schnackenberg 5100
6 P. Wisse 2700
7 M. Hidayathullah ... 1500
8 T. P 1200
9 G. Lambregts 1100
9 . Lauri 1100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48352
Total active users1553
Act. users last 24h2
Act. users last hour0
Registered user hits last week279
Registered user hits last month926
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, 32 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, 32 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, 32 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, 31 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