Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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');
share|improve this answer
    
Additional questions here would be: how to do unit testing? How to formalize error handling? What is instrumentation? How to improve reporting method? –  mnowotka Oct 26 '12 at 11:04
1  
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 Oct 26 '12 at 12:44
1  
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 Oct 26 '12 at 12:44
1  
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 Oct 26 '12 at 12:45
1  
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. –  Leigh Riffel Oct 26 '12 at 12:45
show 1 more comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.