on_off constraints

on_off_constraints

SET SERVER OUTPUT ON;
WHENEVER SQLERROR EXIT;

DECLARE
  v_sql VARCHAR2(32000);

  CURSOR c_disableConstraints IS

    SELECT 'alter table '||table_name||' disable constraint '||constraint_name sqlstatement
    FROM USER_CONSTRAINTS
    WHERE constraint_type IN ('R','U','P')
    ORDER BY DECODE (CONSTRAINT_TYPE, 'R', 1, 'U', 2, 'P', 3);

  CURSOR c_enableConstraints IS

    SELECT 'alter table '||table_name||' enable constraint '||constraint_name sqlstatement
    FROM USER_CONSTRAINTS
    WHERE constraint_type IN ('R','U','P')
    ORDER BY DECODE (CONSTRAINT_TYPE, 'R', 1, 'U', 2, 'P', 3) DESC;

BEGIN

  FOR v_disableConstraint IN c_disableConstraints LOOP
    v_sql := v_disableConstraint.sqlstatement;
    DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE (v_sql);
  END LOOP;

  FOR v_enableConstraint IN c_enableConstraints LOOP
    v_sql := v_enableConstraint.sqlstatement;
    DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE (v_sql);
  END LOOP;

END
;

Disable and enable all constraints (respecting order (FK/UC/PK))