CODEWITHSUNDEEP
oracle-databaseplsqlparameter-passing
kabdul
kabdul

Reputation: 29

Disable/enable constraints in an Oracle table using a Stored Proc where table name is Parametrized

I would like to create a reusable Stored Procedure which can disable constraints in a table, when that table is passed as a parameter. I created a stored procedure which has the table name hard coded.Could you please help

Create or Replace Procedure Disable_Constraints
as Begin  
Declare 
V_OWNER VARCHAR2(30);  
V_TABLE_NAME VARCHAR2(30);  
V_CONSTRAINT_NAME VARCHAR2(30);  
CURSOR C IS   
select OWNER,TABLE_NAME,CONSTRAINT_NAME  
from all_constraints  
where table_name='EMP_CONSTRAINTS';  
BEGIN  
OPEN C;  
LOOP  
FETCH C INTO V_OWNER,V_TABLE_NAME,V_CONSTRAINT_NAME;  
EXIT WHEN C%NOTFOUND;  
dbms_utility.exec_ddl_statement ('alter table ' || V_OWNER || '.' || V_TABLE_NAME || ' disable constraint ' || V_CONSTRAINT_NAME);  
END LOOP;  
CLOSE C;  
END;  
END DISABLE_CONSTRAINTS;

Thanks, Khader

Upvotes: 0

Views: 492

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

For example:

Sample table:

SQL> CREATE TABLE test
  2  (
  3     id    NUMBER CONSTRAINT pkt PRIMARY KEY,
  4     name  VARCHAR2 (20) CONSTRAINT cht CHECK (name LIKE 'A%')
  5  );

Table created.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE p_disc (par_table_name IN VARCHAR2)
  2  IS
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT constraint_name
  6              FROM user_constraints
  7             WHERE UPPER (table_name) =
  8                      UPPER (DBMS_ASSERT.sql_object_name (par_table_name)))
  9     LOOP
 10        EXECUTE IMMEDIATE
 11              'alter table '
 12           || DBMS_ASSERT.sql_object_name (par_table_name)
 13           || ' disable constraint '
 14           || cur_r.constraint_name;
 15     END LOOP;
 16  END;
 17  /

Procedure created.

Testing:

SQL> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST';

CONSTRAINT_NAME                STATUS
------------------------------ --------
CHT                            ENABLED
PKT                            ENABLED

SQL> EXEC p_disc('test');

PL/SQL procedure successfully completed.

SQL> SELECT constraint_name, status FROM user_constraints WHERE table_name = 'TEST';

CONSTRAINT_NAME                STATUS
------------------------------ --------
CHT                            DISABLED
PKT                            DISABLED

SQL>

Upvotes: 3

Related Questions