“FK_GR_DEV_1″类型的约束对象在模式”TEST”中未找到。

huangapple go评论63阅读模式
英文:

object "FK_GR_DEV_1" of type CONSTRAINT not found in the schema "TEST"

问题

I am trying to insert the constraints definitions in a temp table with the following request.

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

This request raise the error :

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.

When I check this constraint exists on the current owner ('TEST') I get the name of the constraint:

SELECT constraint_name FROM user_constraints WHERE constraint_name = 'FK_GR_DEV_1' AND owner = 'TEST';

So the constraint exists.

How to modify the first request to achieve my goal?

英文:

I am trying to insert the constraints definitions in a temp table with the following request.

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

This request raise the error :

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.

When I check this constraint exists on the current owner ('TEST') I get the name of the constraint:

SELECT constraint_name FROM user_constraints WHERE constraint_name = 'FK_GR_DEV_1' AND owner = 'TEST';

So the constraint exist.

How to modify the first request to achieve my goal ?

答案1

得分: 1

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

英文:

For dbms_metadata, foreign keys (dba_constraints.constraint_type = 'R') are type REF_CONSTRAINT, not type CONSTRAINT. The dbms_metadata 'CONSTRAINT' type is used for all others (P, U, C).

huangapple
  • 本文由 发表于 2023年4月17日 21:55:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76035938.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定