Oracle Procedure to Enable/Disable all constraints

Question

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.

Answer 1

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');

Answer 2

A very simple procedure for this could look something like:

CREATE OR REPLACE PROCEDURE sp_toggle_constraints (in_enable in boolean) is
begin

  if in_enable = false then
    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;
  else
    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||' enable constraint '||i.constraint_name||'';
    end loop;
  end if;


--This procedure could be made more elegant, that will be left as an exercise for the reader ;)
end;

you might want to read this (document for CREATE PROCEDURE) as well: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm#SQLRF01309