Oracle Scripts - dbapool.com

REM: Script to enable/disable Constraints
REM:
REM: Author: Jyoti
REM: Date Submitted: 04-Mar-2005
REM: Date Posted: 04-Mar-2005
REM:
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

Prompt Enter Owner: 
ACCEPT i_owner;

Prompt Enter TABLE_NAME: 
ACCEPT i_tab_name;

Prompt Enter Column_name: 
ACCEPT i_col_name;

Prompt Enter Constraint_Type (U -UNIQUE, N-not null , P - PRIMARY KEY, R-Reference, C-CHECK) : 
ACCEPT i_cons_type;

Prompt Do you want ENABLE(Y) OR DISABLE (N) the constraint ?
ACCEPT  i_enable_disable;

Prompt Do you want  VALIDATE (Y) OR NOVALIDATE  (N) Clause ?
ACCEPT  i_validate;

DECLARE
BEGIN
         EXECUTE Immediate  ' Drop table LONG_TEST';                                                          
EXCEPTION
         WHEN OTHERS THEN
         NULL;
END;         
/
 
DECLARE
       l_type                           varchar2(1) := 'C';
BEGIN
            IF upper('&&i_cons_type') IN ('C','N') THEN
            
                            EXECUTE Immediate  ' Create table LONG_TEST as select a.constraint_name,
                                                                                   to_lob(search_condition) as search_condition 
                                                                         from All_Cons_Columns a,
                                                                                  All_Constraints       b
                                                                         WHERE    a.owner = b.Owner
                                                                          AND        a.constraint_name = b.constraint_name
                                                                          AND        a.table_name      = b.table_name
                                                                          AND        a.owner  = upper('||'''&&i_owner'''||')
                                                                          AND        a.table_name =  upper('||'''&&i_tab_name'''||')
                                                                          AND        a.column_name =  upper('||'''&&i_col_name'''||')
                                                                          AND        b.constraint_type = '||'''C''' ;
            ELSE
                            EXECUTE Immediate  ' Create table LONG_TEST as select a.constraint_name,
                                                                                   to_lob(search_condition) as search_condition 
                                                                         from All_Cons_Columns a,
                                                                                  All_Constraints       b
                                                                         WHERE    a.owner = b.Owner
                                                                          AND        a.constraint_name = b.constraint_name
                                                                          AND        a.table_name      = b.table_name
                                                                          AND        a.owner  = upper('||'''&&i_owner'''||')
                                                                          AND        a.table_name =  upper('||'''&&i_tab_name'''||')
                                                                          AND        a.column_name =  upper('||'''&&i_col_name'''||')
                                                                          AND        b.constraint_type =  upper('||'''&&i_cons_type'''||')';
            END IF;                                                                                                                          
END;
/

DECLARE 
           CURSOR c_constraint_name IS
           SELECT  a.constraint_name
                          FROM All_Cons_Columns a,
                                    All_Constraints       b
                          WHERE    a.owner = b.Owner
                          AND        a.constraint_name = b.constraint_name
                          AND        a.table_name      = b.table_name
                          AND        a.owner  = upper('&&i_owner')
                          AND        a.table_name =  upper('&&i_tab_name')
                          AND        a.column_name =  upper('&&i_col_name')
                          AND        b.constraint_type =  upper('&&i_cons_type');
                          
           TYPE  RefCurTyp IS REF CURSOR;                      
           c_cons_name                RefCurTyp;             
           l_enable_disable           VARCHAR2(10);
           l_constraint                   All_Cons_Columns.constraint_name%TYPE;
           l_query                          Varchar2(2000);    
           l_validate                       VARCHAR2(30);

BEGIN
           IF upper( '&&i_enable_disable') = 'Y' THEN
                     l_enable_disable := 'enable';
            ELSE
                     l_enable_disable := 'disable';                  
            END IF;
            
            IF upper('&&i_validate') = 'Y' THEN
                          l_validate := ' validate ';
            ELSE
                          l_validate := ' novalidate ';
            END IF;
                          
            IF upper('&&i_cons_type') IN ('C','N') THEN
                 IF upper('&&i_cons_type') = 'N' THEN
                       l_query :=  'Select constraint_name
                                                from LONG_TEST 
                                              where search_condition  '||'   LIKE  '||'''%IS NOT NULL%''';
                 ELSIF  upper('&&i_cons_type') = 'C' THEN              
                       l_query :=  'Select constraint_name
                                                from LONG_TEST 
                                              where search_condition  '||'   NOT LIKE  '||'''%IS NOT NULL%''';                                                                           
                 END IF;    
               
                 OPEN c_cons_name FOR l_query;
                 FETCH c_cons_name INTO l_constraint;
                 IF c_cons_name%FOUND THEN
                           EXECUTE IMMEDIATE 'Alter table '||'&&i_tab_name'||'  '||l_enable_disable||l_validate||'   constraint   '|| l_constraint;
                 END IF;                           
                 CLOSE c_cons_name;

ELSE 
                   OPEN  c_constraint_name;
                   FETCH c_constraint_name INTO l_constraint;
                   IF c_constraint_name%FOUND THEN
                           EXECUTE IMMEDIATE 'Alter table '||'&&i_tab_name'||'  '||l_enable_disable||l_validate||'   constraint   '|| l_constraint;
                   END IF;
                   CLOSE  c_constraint_name;                                 
            END IF;
            EXECUTE Immediate  ' Drop table LONG_TEST';                                                          
END;
/

Home Discussion Forum FAQs Articles Jobs Newsletters Directory Downloads

Our Premium Sponsor