问题

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?

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.

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

答案1

得分: 1

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