Wednesday, January 28, 2009

ORA-31607: function FETCH_DDL is inconsistent with transform

Always check out the original article at 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.
trans_handle := DBMS_METADATA.ADD_TRANSFORM(exp_handle, 'DDL');
ddls := DBMS_METADATA.FETCH_DDL(exp_handle);
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.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles