英文:
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
,而不是 CONSTRAINT
。dbms_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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论