Oracle Procedure to Enable/Disable all constraints

constraintmigrationoraclestored-procedures

I have this nice piece of code:

begin
for i in 
(
    select constraint_name, table_name 
    from user_constraints 
    where constraint_type ='R'
    and status = 'ENABLED'
) LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/

I'm using if very often for my migration so it would be even better to create a procedure out of if. The procedure should accept one boolean argument (should it enable or disable constraints), but frankly I had never wrote a procedure before. Any help would be appreciated.

Best Answer

I recommend you put the code in an AUTHID CURRENT_USER package. You can certainly pass a Boolean, but I think a Varchar2 makes the usage clear. You should unit test this code, add documentation, formalize the error handling, include instrumentation, and probably modify the reporting method. 

CREATE OR REPLACE PACKAGE Maint AUTHID CURRENT_USER IS

Procedure ToggleConstraints (iNewStatus In Varchar2);

END;
/


CREATE OR REPLACE PACKAGE BODY Maint IS

Procedure ToggleConstraints (iNewStatus In Varchar2) Is
Begin
   If (UPPER(iNewStatus) NOT IN ('ENABLED','DISABLED')) Then
      Raise_Application_Error(-20001
         , 'Constraints can only be toggled to ENABLED OR DISABLED.');
   End If;

   For vConstraint In 
   (
       SELECT 'alter table ' || table_name      
         || DECODE(UPPER(iNewStatus), 'DISABLED',' disable',' enable')
         || ' constraint ' || constraint_name As Statement
       FROM user_constraints 
       WHERE constraint_type = 'R' 
       AND status = DECODE(UPPER(iNewStatus),'DISABLED','ENABLED','DISABLED')
   ) Loop
      DBMS_Output.Put_Line(vConstraint.Statement);
      execute immediate vConstraint.Statement;
   End loop;
End;


END;
/

Set serveroutput on size 1000000 format wrapped
EXECUTE Maint.ToggleConstraints(iNewStatus=>'ENABLED');
Related Question