ORA-31603: object "XYZ" of type CONSTRAINT not found in schema ...

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Here is a classical situation where I don't know if I am staring at a bug, at a feature or I am simply drunk.
I am executing DBMS_METADATA.GET_DDL in an Oracle XE (10g) instance, in order to get the DDL for the table constraint as an ALTER statement, but the function throws a weird exception saying that such object is not in my schema. On the contrary, if I execute DBMS_METADATA.GET_DEPENDENT_DDL, everything works normally.

create table master_table (id number, col_1 number, col_2 number);
alter table master_table add constraint pk_master_table primary key(id);

create table detail_table (id number, master_id number, col_1 number, col_2 number);
alter table detail_table add constraint pk_detail_table primary key(id);
alter table detail_table add constraint fk_detail_table_master foreign key(master_id) references master_table(id);

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name) DDL
FROM USER_CONSTRAINTS
WHERE table_name = 'DETAIL_TABLE';

select dbms_metadata.get_dependent_ddl( 'REF_CONSTRAINT', table_name) DDL
FROM USER_TABLES
WHERE table_name = 'DETAIL_TABLE';


table MASTER_TABLE created.
table MASTER_TABLE altered.
table DETAIL_TABLE created.
table DETAIL_TABLE altered.
table DETAIL_TABLE altered.

Error starting at line 8 in command:
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name) DDL
FROM USER_CONSTRAINTS
WHERE table_name = 'DETAIL_TABLE'
Error report:
SQL Error: ORA-31603: object "FK_DETAIL_TABLE_MASTER" of type CONSTRAINT not found in schema "TEST"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
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 dbms_metadata.get_dependent_ddl( 'REF_CONSTRAINT', table_name) DDL
FROM USER_TABLES
WHERE table_name = 'DETAIL_TABLE';

DDL
------------------------------------------------
ALTER TABLE "TEST"."DETAIL_TABLE" ADD CONSTRAINT "FK_DETAIL_TABLE_MASTER" FOREIGN KEY ("MASTER_ID")
REFERENCES "TEST"."MASTER_TABLE" ("ID") ENABLE

Interestingly enough, if I restrain the query to the primary key constraint, it works:

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name) DDL
FROM USER_CONSTRAINTS
WHERE table_name = 'DETAIL_TABLE'
and constraint_type = 'P';

DDL
------------------------------------------------
ALTER TABLE "TEST"."DETAIL_TABLE" ADD CONSTRAINT "PK_DETAIL_TABLE" PRIMARY KEY ("ID")

...


But it fails if on the foreign key:

Error starting at line 8 in command:
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name) DDL
FROM USER_CONSTRAINTS
WHERE table_name = 'DETAIL_TABLE'
and constraint_type = 'R'
Error report:
SQL Error: ORA-31603: object "FK_DETAIL_TABLE_MASTER" of type CONSTRAINT not found in schema "TEST" 

See message translations for ORA-31603 and search additional resources.