This is purely a reminder for me next time, getting the output from a begin block, vs. not.
It seems that a being block runs on the server without coming back to the client, this is nice for fast reasons, but crappy for feedback. You can understand why too. If the server is busy running all of your statements, then you are not going to get the output back.
And guess what, heaps of the cool statements can only be run on server code, which does make total sense too. Oh, also there is no fflush with dbms_output - so my blog title is a little misleading.
It's an important fact to remember, all your directives with setting echo on, they are for the client. So spool is a sqlplus command not a PL/SQL command – BOOM! Mind blown!
This most might also help you purge your work centre data, as quite often [for some more than others] you might get a few too many records in this area.
set echo on
set feedback on
set timing on
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
spool truncateUselessWorkCentre.log
DELETE from TWEDTA.F00166 where GTOBNM='GT01131'
AND GTTXKY IN (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
commit;
DELETE from TWEDTA.F00165 where GDOBNM='GT01131'
AND GDTXKY IN (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
commit;
DELETE from TWEDTA.F01131T where
ZCSERK in (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
commit;
begin
for a in 119200..119228 loop
dbms_output.put_line('06 about to process date' || to_char(a));
DELETE from TWEDTA.F01131M where ZMAN8 = 99000006 and zmdti = a;
commit;
dbms_output.put_line('07 about to process date' || to_char(a));
DELETE from TWEDTA.F01131M where ZMAN8 = 99000007 and zmdti = a;
commit;
dbms_output.put_line('13 about to process date' || to_char(a));
DELETE from TWEDTA.F01131M where ZMAN8 = 99000013 and zmdti = a;
commit;
end loop;
end;
/
DELETE from TWEDTA.F01133 where
ZTSERK in (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
commit;
DELETE from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013);
commit;
spool off;
quit;
gives the output:
SQL> DELETE from TWEDTA.F00166 where GTOBNM='GT01131'
2 AND GTTXKY IN (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
8155 rows deleted.
Elapsed: 00:00:02.75
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> DELETE from TWEDTA.F00165 where GDOBNM='GT01131'
2 AND GDTXKY IN (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
8155 rows deleted.
Elapsed: 00:00:04.48
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> DELETE from TWEDTA.F01131T where
2 ZCSERK in (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
2330 rows deleted.
Elapsed: 00:00:00.36
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> begin
2 for a in 119200..119228 loop
3 dbms_output.put_line('06 about to process date' || to_char(a));
4 DELETE from TWEDTA.F01131M where ZMAN8 = 99000006 and zmdti = a;
5 commit;
6 dbms_output.put_line('07 about to process date' || to_char(a));
7 DELETE from TWEDTA.F01131M where ZMAN8 = 99000007 and zmdti = a;
8 commit;
9 dbms_output.put_line('13 about to process date' || to_char(a));
10 DELETE from TWEDTA.F01131M where ZMAN8 = 99000013 and zmdti = a;
11 commit;
12 end loop;
13 end;
14 /
--but, this just comes at once, as there is no concept of fflush for the server, you need to wait for the code to return
06 about to process date119200
07 about to process date119200
13 about to process date119200
06 about to process date119201
07 about to process date119201
13 about to process date119201
06 about to process date119202
07 about to process date119202
13 about to process date119202
06 about to process date119203
07 about to process date119203
13 about to process date119203
06 about to process date119204
07 about to process date119204
13 about to process date119204
06 about to process date119205
07 about to process date119205
13 about to process date119205
06 about to process date119206
07 about to process date119206
13 about to process date119206
06 about to process date119207
07 about to process date119207
13 about to process date119207
06 about to process date119208
07 about to process date119208
13 about to process date119208
06 about to process date119209
07 about to process date119209
13 about to process date119209
06 about to process date119210
07 about to process date119210
13 about to process date119210
06 about to process date119211
07 about to process date119211
13 about to process date119211
06 about to process date119212
07 about to process date119212
13 about to process date119212
06 about to process date119213
07 about to process date119213
13 about to process date119213
06 about to process date119214
07 about to process date119214
13 about to process date119214
06 about to process date119215
07 about to process date119215
13 about to process date119215
06 about to process date119216
07 about to process date119216
13 about to process date119216
06 about to process date119217
07 about to process date119217
13 about to process date119217
06 about to process date119218
07 about to process date119218
13 about to process date119218
06 about to process date119219
07 about to process date119219
13 about to process date119219
06 about to process date119220
07 about to process date119220
13 about to process date119220
06 about to process date119221
07 about to process date119221
13 about to process date119221
06 about to process date119222
07 about to process date119222
13 about to process date119222
06 about to process date119223
07 about to process date119223
13 about to process date119223
06 about to process date119224
07 about to process date119224
13 about to process date119224
06 about to process date119225
07 about to process date119225
13 about to process date119225
06 about to process date119226
07 about to process date119226
13 about to process date119226
06 about to process date119227
07 about to process date119227
13 about to process date119227
06 about to process date119228
07 about to process date119228
13 about to process date119228
PL/SQL procedure successfully completed.
Elapsed: 00:08:47.17
SQL> DELETE from TWEDTA.F01133 where
2 ZTSERK in (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013));
10484 rows deleted.
Elapsed: 00:00:00.91
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> DELETE from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013);
10484 rows deleted.
Elapsed: 00:00:02.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> spool off;
set
echo on
set
feedback on
set
timing on
SET
SERVEROUTPUT ON FORMAT WORD_WRAPPED
col
dt new_value dt
select
to_char(sysdate,'YYYYMMDDHH24MISS') dt from dual;
spool
truncateUselessWorkCentre_daily_&dt.log
select
count(1), 'F00166' from twedta.f00166;
select
count(1), 'F00165' from twedta.f00165;
select
count(1), 'F01131T' from twedta.f01131T;
select
count(1), 'F01131M' from twedta.f01131M;
select
count(1), 'F01133' from twedta.f01133;
select
count(1), 'F01131' from twedta.f01131;
DECLARE
fromdate
number;
todate
number;
begin
select (to_char(sysdate-6, 'YYYYDDD')-1900000) into fromdate from dual;
select (to_char(sysdate-3, 'YYYYDDD')-1900000) into todate from dual;
for a in fromdate..todate loop
DELETE from TWEDTA.F00166 where GTOBNM='GT01131'
AND GTTXKY IN (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006,
99000007, 99000013) and ZZDTI = a);
commit;
DELETE from TWEDTA.F00165 where GDOBNM='GT01131'
AND GDTXKY IN (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006,
99000007, 99000013) and ZZDTI = a);
commit;
DELETE from TWEDTA.F01131T where
ZCSERK in (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007,
99000013) and ZZDTI = a);
commit;
dbms_output.put_line('06 about to process date' || to_char(a));
DELETE from TWEDTA.F01131M where ZMAN8 = 99000006 and zmdti = a;
commit;
dbms_output.put_line('07 about to process date' || to_char(a));
DELETE from TWEDTA.F01131M where ZMAN8 = 99000007 and zmdti = a;
commit;
dbms_output.put_line('13 about to process date' || to_char(a));
DELETE from TWEDTA.F01131M where ZMAN8 = 99000013 and zmdti = a;
commit;
DELETE from TWEDTA.F01133 where
ZTSERK in (SELECT ZZSERK from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007,
99000013) and ZZDTI = a);
commit;
DELETE from TWEDTA.F01131 where ZZAN8 in (99000006, 99000007, 99000013)
and ZZDTI = a;
commit;
end loop;
end;
/
select
count(1), 'F00166' from twedta.f00166;
select
count(1), 'F00165' from twedta.f00165;
select
count(1), 'F01131T' from twedta.f01131T;
select
count(1), 'F01131M' from twedta.f01131M;
select
count(1), 'F01133' from twedta.f01133;
select
count(1), 'F01131' from twedta.f01131;
spool
off;
quit;
No comments:
Post a Comment