oracle 在架构“TEST”中未找到CONSTRAINT类型的对象“FK_GR_DEV_1”

p5cysglq  于 2023-04-20  发布在  Oracle
关注(0)|答案(1)|浏览(180)

我正尝试使用以下请求将约束定义插入到临时表中。

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

所以限制是存在的。
如何修改第一个请求以实现我的目标?

d4so4syb

d4so4syb1#

对于dbms_metadata,外键(dba_constraints.constraint_type = 'R')是REF_CONSTRAINT类型,而不是CONSTRAINT类型。dbms_metadata ' CONSTRAINT '类型用于所有其他类型(P、U、C)。

相关问题