Script to enable/disable Constraints

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;
/