Skip to main content

CREATE SCRIPT TO DISABLE AND ENABLE CONSTRAINT

DISABLE

SELECT 'ALTER TABLE "' || A.TABLE_NAME || '" DISABLE CONSTRAINT "' || A.CONSTRAINT_NAME || '";' ENABLE_CONSTRAINTS
FROM   USER_CONSTRAINTS A
WHERE  
       A.CONSTRAINT_TYPE = 'R' 
AND    A.R_CONSTRAINT_NAME IN (SELECT A1.CONSTRAINT_NAME
                               FROM   USER_CONSTRAINTS A1
                               WHERE  A1.TABLE_NAME = DECODE(UPPER('&1'),'ALL',A.TABLE_NAME,UPPER('&1'))
                               );




ENABLE






SELECT 'ALTER TABLE "' || A.TABLE_NAME || '" ENABLE CONSTRAINT "' || A.CONSTRAINT_NAME || '";'
FROM   USER_CONSTRAINTS A
WHERE  A.CONSTRAINT_TYPE = 'R'
AND    A.TABLE_NAME      = DECODE(UPPER('&1'),'ALL',A.TABLE_NAME,UPPER('&1'))
AND    A.STATUS          = 'DISABLED';







































Comments











Post a Comment




















Popular posts from this blog



















Insert script with multiple cursors and condition check




























  DECLARE  CURSOR C1  IS    SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');    CURSOR C2      IS        SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');                CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)          IS            SELECT COUNT(*) AS COUNT_UP            FROM TABLE_NAME_3            WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID            AND COLUMN_CONDITION_02=CP_TABLE_01_ID;  COUNT_UP NUMBER;  BEGIN  FOR R1 IN C1  LOOP      FOR R2 IN C2      LOOP            OPEN C3(R1.ID,R2.ID);         FETCH C3 INTO COUNT_UP;         CLOSE C3;                 IF (COUNT_UP=0) THEN            INSERT            INTO TABL...




















Post a Comment









Read more





























REF Cursor




























REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC.   DECLARE  TYPE C1 IS  REF CURSOR ; CURSOR  C IS  SELECT  *  FROM  DUAL; REF_CURSOR RC; BEGIN  IF  (TO_CHAR(SYSDATE, 'DD' ) =  30  ) THEN  OPEN  REF_CURSOR FOR  'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) =  29  ) THEN  OPEN  REF_CURSOR FOR  SELECT  *  FROM  TABLE2; ELSE  OPEN  REF_CURSOR FOR  SELECT  *  FROM  DUAL; END  IF ; OPEN  C; END ;  




















Post a Comment









Read more