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.
declareInterestingly 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.
exp_handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT','COMPATIBLE', 'ORACLE');
trans_handle := DBMS_METADATA.ADD_TRANSFORM(exp_handle, 'DDL');
ddls := DBMS_METADATA.FETCH_DDL(exp_handle);
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.