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 |
Partager