No question at this time
DBA Top 10
1 M. Cadot 6600
2 B. Vroman 3400
3 J. PĂ©ran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 900
6 P. Wisse 600
7 T. Boles 400
7 D. Walgude 400
7 D. Johnson 400
10 J. Alcroft 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48675
Total active users1328
Act. users last 24h0
Act. users last hour0
Registered user hits last week23
Registered user hits last month452
Go up

Dynamic Sql
Next thread: Doubt related to join
Prev thread: Data Migration

Message Score Author Date
Hi I have the following sql statement that pro...... Stan M Jan 17, 2014, 15:48
set heading on set pagesize 13 set feedback off ...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Philip Wisse Jan 17, 2014, 15:57
Hello Stan, Philip's solution is really funny a...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Jan 18, 2014, 12:42
if you only used NTILE(10000) instead of NTILE(1...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Zoran Hudec Jan 18, 2014, 22:01

Follow up by mail Click here


Subject: Dynamic Sql
Author: Stan M, South Africa
Date: Jan 17, 2014, 15:48, 3088 days ago
Os info: linux
Oracle info: 10
Message: Hi

I have the following sql statement that produces 1000 insert statements like this:

nsert /*+ APPEND */ into test.DATA select data_id, data_size, item_data from user.DATA@pres10g where data_id between 42365 and 19893137;

SELECT MIN(data_id) min_data_id,
MAX(data_id) max_data_id,
COUNT(*) cnt_in_range,
RANGE
FROM (SELECT data_id, NTILE(1000) OVER(ORDER BY data_id) RANGE
FROM aesuser.pritemdata)
GROUP BY RANGE;


can you help me with a dynamic sql to group the 1000 insert outputs in groups of 10 and put a commit after every insert?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Dynamic Sql
Author: Philip Wisse, Netherlands
Date: Jan 17, 2014, 15:57, 3088 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message: set heading on
set pagesize 13
set feedback off
select 'insert statement'||ltrim(rownum) as "commit;" from dual connect by level < 20;

commit;
--------------------------------------------------------
insert statement1
insert statement2
insert statement3
insert statement4
insert statement5
insert statement6
insert statement7
insert statement8
insert statement9
insert statement10

commit;
--------------------------------------------------------
insert statement11
insert statement12
insert statement13
insert statement14
insert statement15
insert statement16
insert statement17
insert statement18
insert statement19
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Dynamic Sql
Author: Bruno Vroman, Belgium
Date: Jan 18, 2014, 12:42, 3088 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Stan,

Philip's solution is really funny and imaginative, but maybe you want to work with something else than "sqlplus". So here is another solution:
SELECT 'insert /*+ APPEND */ into test.data select... between ' ||

TO_CHAR( min_data_id ) ||
' and ' ||
TO_CHAR( max_data_id ) || ';' ||
CASE WHEN MOD( rownum, 10 ) = 0
THEN CHR(10) || 'commit;'
END stt
FROM ...
(and you have to add a final "commit").

Best regards,

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

Subject: Re: Dynamic Sql
Author: Zoran Hudec, Australia
Date: Jan 18, 2014, 22:01, 3087 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: if you only used
NTILE(10000) instead of NTILE(1000)
you will get the same result
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here