1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
SET TERM OFF
SET DEF ~
SET ECHO OFF
SET SERVEROUTPUT ON
SET FEED OFF
SET PAGESIZE 0
SET LINESIZE 200
SET VERIFY OFF
SET TERM ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;
DECLARE
reqSelec VARCHAR2(200);
vtable VARCHAR2(200);
TYPE Curseur IS REF CURSOR;
Cur Curseur;
TYPE tROW IS TABLE OF ROWID;
liste tROW;
NBMAXCOMMIT NUMBER :=10000;
BEGIN
-- Recuperation du nom de la table
vtable :='~1';
-- Requete permettant d'identifier les enregistrements à supprimer
reqSelec:= 'SELECT ROWID FROM ' || vtable;
-- Début du traitement
OPEN Cur FOR reqSelec;
LOOP
FETCH Cur BULK COLLECT INTO liste LIMIT NBMAXCOMMIT;
EXIT WHEN liste.count = 0;
FOR i in liste.first.. liste.last
LOOP
EXECUTE IMMEDIATE 'DELETE FROM ' || vtable || ' WHERE ROWID = :1' using liste(i);
END LOOP;
COMMIT;
EXIT WHEN Cur%NOTFOUND;
END LOOP;
CLOSE Cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('VMSG_RETOUR = ' || SUBSTR(TO_CHAR(SQLCODE)||':'||SQLERRM,1,250) || CHR(10));
END;
/
EXIT |
