1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| Test Case 3 – Delete using anonymous PL/SQL block, looping through the cursor, and deleting one tow at a time.
Loop has counter, programmed to trigger commit after each 1000 records.
SQL> declare
rec_read number(6) := 0;
rec_rest number(6) := 0;
vc_var_out varchar2(41) := 'Delete Archive Data';
cursor rec_sql is select SECOND_COLL,rowid from artist_test
where SECOND_COLL='JAVA CLASS' order by SECOND_COLL,rowid;
begin
for rec_sql_run in rec_sql loop
rec_read := rec_read + 1 ;
rec_rest := rec_rest + 1 ;
delete from artist_test A where A.rowid = rec_sql_run.rowid ;
if rec_rest > 1000 THEN
COMMIT;
rec_rest := 1;
dbms_output.put_line('COMMIT AT..'|| rec_read);
END IF;
end loop;
end;
/ |
