我正尝试使用以下请求将约束定义插入到临时表中。
DECLARE
v_ddl VARCHAR2(4000);
BEGIN
FOR c IN (SELECT DISTINCT table_name FROM user_constraints WHERE owner = 'TEST'
AND constraint_name NOT LIKE 'BIN$%'
AND constraint_name NOT LIKE 'IMPDP_%'
AND constraint_name NOT LIKE 'RUPD$%'
AND constraint_name NOT LIKE 'MLOG$%'
AND constraint_name NOT LIKE 'UET$%'
AND constraint_name NOT LIKE 'AQ$%'
AND constraint_name NOT LIKE 'MDRT_%'
AND constraint_name NOT LIKE 'SDO_%')
LOOP
FOR c2 IN (SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = c.table_name AND owner = 'TEST')
LOOP
v_ddl := dbms_metadata.get_ddl('CONSTRAINT', c2.constraint_name);
INSERT INTO temp_constraints (constraint_name, table_name, table_definition, constraint_type)
VALUES (c2.constraint_name, c.table_name, v_ddl, c2.constraint_type);
END LOOP;
END LOOP;
END
此请求引发错误:
ORA-31603: object "FK_GR_DEV_1" of type CONSTRAINT not found in the schema "TEST"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6718
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at ligne 16
ORA-06512: at ligne 16
31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""
*Cause: The specified object was not found in the database.
*Action: Correct the object specification and try the call again.
当我检查当前所有者('TEST')上是否存在此约束时,我将获得约束的名称:
SELECT constraint_name FROM user_constraints WHERE constraint_name = 'FK_GR_DEV_1' AND owner = 'TEST';
所以限制是存在的。
如何修改第一个请求以实现我的目标?
1条答案
按热度按时间d4so4syb1#
对于
dbms_metadata
,外键(dba_constraints.constraint_type = 'R'
)是REF_CONSTRAINT
类型,而不是CONSTRAINT
类型。dbms_metadata 'CONSTRAINT
'类型用于所有其他类型(P、U、C)。