Tuesday, April 22, 2008

SQL Script to enable and disable Constraints in any schema owner or user of Oracle Databse

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

No comments: