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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
|
CREATE OR REPLACE package body SPA_DATA.PA_SPA as
procedure P_SPA_ACTIVATE_FK (PTABLENAME varchar2,PMODE varchar2) as
/******************************************************************************
NAME: P_SPA_ACTIVATE_FK
PURPOSE: Fonction permettant d'activer ou de desactiver les contraintes
portant sur une table
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 02/08/2007 FBE Creation
NOTES:
ptablename: Nom de la table devant etre isolée des contraintes de clés
étrangeres
pmode: A pour activer les contraintes portant sur la table
D pour desactiver les contraintes portant sur la table
******************************************************************************/
CURSOR c
IS
SELECT uc.constraint_name, uc.constraint_type, uc.table_name
FROM user_constraints uc,
(SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = PTABLENAME AND constraint_type = 'P') table_c
WHERE uc.r_constraint_name = table_c.constraint_name
AND uc.table_name not like '%GLD%'
AND uc.constraint_type = 'R';
vsql_string VARCHAR2 (2000);
vmode VARCHAR2 (10);
BEGIN
CASE pmode
WHEN 'D'
THEN
vmode := 'DISABLE';
WHEN 'A'
THEN
vmode := 'ENABLE';
END CASE;
FOR v_c IN c
LOOP
vsql_string :=
'ALTER TABLE '
|| v_c.table_name
|| ' '
|| vmode
|| ' CONSTRAINT '
|| v_c.constraint_name;
EXECUTE IMMEDIATE vsql_string;
END LOOP;
COMMIT;
END p_spa_activate_fk;
procedure P_SPA_ACTIVATE_GEN_FK (PMODE varchar2) as
CURSOR C IS
SELECT TABLE_NAME
FROM USER_TABLES WHERE TABLE_NAME NOT LIKE 'TT%' ;
BEGIN
FOR V_C IN C LOOP
-- Appeler la procédure P_SPA_ACTIVATE_FK
P_SPA_ACTIVATE_FK ( V_C.TABLE_NAME, PMODE );
DBMS_OUTPUT.PUT_LINE(V_C.TABLE_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Fini');
COMMIT;
END P_SPA_ACTIVATE_GEN_FK;
end PA_SPA;
/ |
Partager