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.