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.

ORA-31609: error loading file "kuauddef.xsl" from file system directory

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

Sometimes i wish certain things were a little bit easier.
I was attempting to import a datapump export obtained with the DBMS_METADATA "demo" export program i mentioned a while ago, but unfortunately it blows up while processing trigger information.
After investigating the problem, it seemed that, for some reason, there were missing stylesheets that needed to be loaded using a script called initmeta.sql, under the $ORACLE_HOME/rdbms/admin folder according to a user who reported that after doing that, the problem was fixed, but to my surprise i could not find such script in my Oracle XE linux installation.
So i began another iteration, in pursue of a solution for this new "problem" and i found an OTN forum discussion where someone else could not spot the fatal script, but a DBA published the content of initmeta.sql for Oracle version 10gR1, which is a single line:

EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('/Users/oracle/.../rdbms/xml/xsl');

(whole path omitted)

Apparently i only needed to modify the file path, but first i wanted to have a look at the package specification of DBMS_METADATA_UTIL.
Interestingly enough, in version 10gR2 (XE) something must have been changed, because procedure LOAD_STYLESHEETS doesn't take any parameters.
Oh good, i thought, it means that they are retrieving the path themselves, so i removed the parameter and ran the procedure as SYS:

EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS;

ORA-31609: error loading file "kuauddef.xsl" from file system directory
"/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/xml/xsl"
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1793
ORA-06512: at line 1
31609. 00000 -  "error loading file \"%s\" from file system directory \"%s\""
*Cause:    The installation script initmeta.sql failed to load
    the named file from the file system directory into the database.
*Action:   Examine the directory and see if the file is present
    and can be read.

And there you go, with ORA-31609.
It's curious that SQL Developer explains the error message mentioning the initmeta.sql script that didn't run at all, it means that procedure LOAD_STYLESHEETS is expected to be run only in that occasion.

So, here is where i am stuck now, the missing $ORACLE_HOME/rdbms/xml directory.
May be i'll update this entry if i manage to work around this problem.

Fortunately there are still the good-ole EXP/IMP utilities to keep the ball rolling.

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

ORA-31600: invalid input value 200000001 for parameter HANDLE in function FETCH_CLOB

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

Further to my last posting about DBMS_METADATA related ORA-31607, here is a slightly modified version of an Oracle supplied sample of procedure performing the "dump" of all the objects belonging to the current user. In red color, i highlighted the cause of ORA-31600.
The original program used a table to store all the CLOBs, but i commented out the relevant lines and i merely print out the initial 255 characters of the DDL along with a counter.

PROCEDURE get_schema_md IS

-- Define local variables.
h       NUMBER;         -- handle returned by OPEN
th      NUMBER;         -- handle returned by ADD_TRANSFORM
doc     CLOB;           -- metadata is returned in a CLOB
objs    INTEGER := 0;   -- object counter
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA', user);

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Fetch the objects.
LOOP
                            -- the correct handle is h, not th
doc := DBMS_METADATA.FETCH_CLOB(th, FALSE, DBMS_LOB.CALL);

-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
objs := objs + 1;
-- Store the metadata in the table.
-- INSERT INTO my_metadata(md) VALUES (doc);
-- COMMIT;
dbms_output.put_line(dbms_lob.substr(doc,255,1));
dbms_lob.freetemporary(doc);

END LOOP;

-- Release resources.
DBMS_METADATA.CLOSE(h);
dbms_output.put_line(objs);
END;

ORA-31600: invalid input value 200000001 for parameter HANDLE in function FETCH_CLOB
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 555
ORA-06512: at "SYS.DBMS_METADATA", line 3801
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "TOOLS.GET_SCHEMA_MD", line 21

This is a rather trivial error, it is sufficient to replace the wrong handle name (th), with the correct one (h) to get the program to work. Each of these opaque handlers returned by the DBMS_METADATA functions serves as input to other procedures or functions and this assortment of variables can lead to some confusion, i.e. the exception illustrated in this posting.
The scheme below summarizes the pattern of usage of these handlers, where the number returned by the function on the left hand side can be used as input for the function on the right hand side:

OPEN -> SET_FILTER
    -> SET_COUNT
    -> SET_PARSE_ITEM
    -> ADD_TRANSFORM
    -> FETCH_xxx
    -> GET_QUERY
    -> CLOSE

OPENW -> CONVERT
     -> PUT
     -> SET_PARSE_ITEM
     -> ADD_TRANSFORM
     -> CLOSE

ADD_TRANSFORM -> SET_TRANSFORM_PARAM
             -> SET_REMAP_PARAM

Note also that if you comment out the line in green color, you'll get an XML formatted output instead of plain text DDLs.

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

ORA-31607: function FETCH_DDL is inconsistent with transform

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

If you wonder why you are getting the following error message attempting to use DBMS_METADATA.FETCH_DDL:

ORA-31607: function FETCH_DDL is inconsistent with transform.

then you might want to ensure that your program contains the line in green color call prior to calling FETCH_DDL.

declare
...
begin
exp_handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT','COMPATIBLE', 'ORACLE');
trans_handle := DBMS_METADATA.ADD_TRANSFORM(exp_handle, 'DDL');
DBMS_METADATA.SET_COUNT(exp_handle, 1);
loop
  ddls.DELETE;
  ddls := DBMS_METADATA.FETCH_DDL(exp_handle);
...
end;

Interestingly enough, after fixing the problem above, i found out that DBMS_METADATA.FETCH_DDL function is almost useless when SCHEMA_EXPORT is specified because i can get only a limited subset of the total objects in the schema.
Changing the number of retrieved objects with SET_COUNT or forcing certain object types with SET_FILTER seems to be irrelevant.
I ignore if this problem affects only Oracle XE and/or earlier/subsequent releases of Oracle.

The problem however can be avoided turning to DBMS_METADATA.FETCH_CLOB function and processing one object at a time. This approach seems to be encouraged by the official documentation.

Am i doing something wrong when processing the results?
Or is FETCH_DDL plain buggy?

I hope to be able to answer these questions one day or another.

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