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.
