Thanks for your help
I was searching this, it generates the ddl to drop and recreate a
constraints and all its dependants.
FUNCTION DB_UTL_DDL_CONSTRAINT( cOwner VARCHAR2, cConstraint VARCHAR2)
RETURN CLOB IS
cReturn CLOB;
cReturn2 CLOB;
cReturn3 CLOB;
cReturn4 CLOB;
cTable VARCHAR2(100);
cCONSTRAINT_TYPE VARCHAR2(100);
BEGIN
/*
SELECT
'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' ADD CONSTRAINT '||CONSTRAINT_NAME
||' CHECK ( '||SEARCH_CONDITION|| ')'||CHR(10)||'/'
SEARCH_CONDITION FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'C' AND OWNER
= 'ADM'
*/
DBMS_OUTPUT.PUT_LINE('1');
SELECT CONSTRAINT_TYPE, TABLE_NAME,CASE WHEN CONSTRAINT_TYPE = 'R' THEN
DBMS_METADATA.GET_DDL('REF_CONSTRAINT',A.CONSTRAINT_NAME,A.OWNER)
WHEN CONSTRAINT_TYPE IN ('U','P') THEN
DBMS_METADATA.GET_DDL('CONSTRAINT',A.CONSTRAINT_NAME,A.OWNER)
END
INTO cCONSTRAINT_TYPE,cTable,cReturn
FROM DBA_CONSTRAINTS A
WHERE OWNER = cOwner AND CONSTRAINT_NAME = cConstraint;
DBMS_OUTPUT.PUT_LINE('2');
DBMS_OUTPUT.PUT_LINE(LENGTH(cReturn));
DBMS_OUTPUT.PUT_LINE('3');
IF cCONSTRAINT_TYPE = 'P' THEN
FOR A IN (SELECT OWNER,CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE R_CONSTRAINT_NAME = cConstraint) LOOP
-- cReturn := cReturn||chr(10)|| a.owner||'-'||a.constraint_name;
SELECT CASE WHEN CONSTRAINT_TYPE = 'R' THEN
DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER)
WHEN CONSTRAINT_TYPE IN ('U','P') THEN
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME,OWNER)
END
INTO cReturn2
FROM DBA_CONSTRAINTS
WHERE OWNER = A.OWNER AND CONSTRAINT_NAME = A.CONSTRAINT_NAME;
cReturn := cReturn || CHR(10)|| '/' ||CHR(10) || cReturn2;
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '||
CONSTRAINT_NAME||';'
INTO cReturn3
FROM DBA_CONSTRAINTS
WHERE OWNER = A.OWNER AND CONSTRAINT_NAME = A.CONSTRAINT_NAME;
cReturn4 := cReturn4 || CHR(10) || cReturn3;
END LOOP;
END IF;
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '||
CONSTRAINT_NAME||';'
INTO cReturn3
FROM DBA_CONSTRAINTS A
WHERE OWNER = cOwner AND CONSTRAINT_NAME = cConstraint;
cReturn4 := cReturn4 || CHR(10) || cReturn3;
cReturn := cReturn4|| CHR(10) || cReturn ;
RETURN cReturn ;
EXCEPTION
WHEN OTHERS THEN
RETURN SQLERRM;
END;
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 17 2004 - 14:30:28 CST
