Le Blog de DiabloCorps

DECLARE
    CURSOR C_Contraintes IS
    SELECT
        Owner
        , Constraint_Name
        , Table_Name
    FROM
        All_Constraints
    WHERE
        Constraint_Type = 'R'
    AND    R_Constraint_Name = 'PK_CATALOG';
    BEGIN
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
        DBMS_OUTPUT.PUT_LINE('--- Desactivation des contraintes ---');
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
        FOR Contrainte IN C_Contraintes LOOP
            DBMS_OUTPUT.PUT_LINE('ALTER TABLE  '||Contrainte.Owner||'.'||Contrainte.Table_Name||' DISABLE CONSTRAINT  "'||Contrainte.Constraint_Name||'";');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
        DBMS_OUTPUT.PUT_LINE('---   Activation des contraintes  ---');
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
        FOR Contrainte IN C_Contraintes LOOP
            DBMS_OUTPUT.PUT_LINE('ALTER TABLE  '||Contrainte.Owner||'.'||Contrainte.Table_Name||' ENABLE CONSTRAINT  "'||Contrainte.Constraint_Name||'";');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
        DBMS_OUTPUT.PUT_LINE('---             Updates            --');
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
        FOR Contrainte IN C_Contraintes LOOP
            DBMS_OUTPUT.PUT_LINE('UPDATE  '||Contrainte.Owner||'.'||Contrainte.Table_Name||' SET CAT_KEY =  :NouvelleValeur WHERE CAT_KEY = :AncienneValeur;');
        END LOOP;
END;
/