Search code examples
oracle-databaseddl

ORA-31603 when generating ddl for constraints

I am trying to generate ddl for constraints. A snippet from the query:

SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner)
FROM   all_constraints
WHERE  owner      = UPPER('THEOWNER')
  AND    constraint_type IN ('U', 'P', 'R');

but getting this: 

ORA-31603: object "EMPLOYEE_DEPNUM_FK" of type CONSTRAINT not found in schema "THEOWNER"
at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1

Running the script as a system user. Also tried the script as the THEOWNER:

SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', 'EMPLOYEE_DEPNUM_FK') from dual;

The same error. I am able to generate ddl for tables, indexes, views etc but for some constraints.

The following query shows the THEOWNER as the owner of the constraint:

SELECT owner FROM all_constraints WHERE CONSTRAINT_NAME='EMPLOYEE_DEPNUM_FK';

I can't seem to find the source code for DBMS_METADATA.GET_DDL anywhere. Any suggestions?

Solution

  • You can use:

    SELECT DBMS_METADATA.get_ddl (CASE WHEN constraint_type = 'R' THEN 'REF_CONSTRAINT' ELSE 'CONSTRAINT' END, constraint_name, owner)
FROM   all_constraints
...

    as ref-constraints have another type description.