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

• mnowotka: Additional questions here would be: how to do unit testing? How to formalize error handling? What is instrumentation? How to improve reporting method?
• Leigh Riffel: Each of these points could take a separate white paper to handle even superficially. The main thing I would recommend is that you read the Oracle Concepts Guide in entirety. oracle.com/pls/db112/to_pdf?pathname=server.112/e25789.pdf I'll post a short comment on each of the points so you can learn more after that.
• Leigh Riffel: A list of Unit Testing options for PL/SQL can be found at en.wikipedia.org/wiki/List_of_unit_testing_frameworks#PL.2FSQL or you can roll your own. You might want to look at this presentation nyoug.org/Presentations/2008/Dec/….
• Leigh Riffel: Concerning error handling, you need to think about how you will do error handling and then be consistent. Read Tom Kytes comments at asktom.oracle.com/pls/asktom/….
• Leigh Riffel: An instrumentation library for Oracle can be found at sourceforge.net/projects/ilo. A video introducing ILO can be found at youtube.com/watch?v=3MHxqF7Zxj8.

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