I am using the below code to extract all DDL for a schema (11gR2).
Is there any way to control the sorting of objects to guarantee the order returned. I looked at both the PL/SQL Packages and Utilities books for 11gR2 and I do not see a way to control this.
schema_handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
ddl_handle := DBMS_METADATA.ADD_TRANSFORM(schema_handle,'DDL');
object_definition := DBMS_METADATA.FETCH_CLOB(schema_handle);
EXIT WHEN object_definition IS NULL;
The users of the SP I created have requested that upon generation of the DDL for a schema, the order of objects remains consistent.
Given that the Oracle documentation does not state and somewhat implies that it controls the fetch order, I posed the question.
NOTE: We are not looking to switch tools to resolve this.
The only way I think you can do this is to switch to object level export, create a loop on user_objects with your order by and then grab ddl (and other dependent ddl/grants etc) in a loop.
There may be other ways, but none come to mind atm.
Edited by: rjamya on Jan 7, 2013 3:13 PM
I created a complete toolkit to generate DDL in Oracle V5.0.17 (yes, many moons ago) in Pro*C (GUI & CL) and I was maintaining it up until 8iR3.
At that point, the Oracle Data Dictionary had become bigger than I had time for, so I never incorporated support for anything that came after 8iR3.
We switched from 8iR3 to 11gR2 in production in November of 2011 and one of the post production tasks is to replace my Pro*C toolkit. In doing this, I wanted to simplify as much as possible the need to know Oracle objects in terms of the Oracle Data Dictionary, thus the code I have.
I started with what you suggested, but saw the simplicity in this and changed direction. I guess you cannot have everything.
Well I suspect Oracle might introduce the functionality eventually (wishful thinking), but metadata_api itself is a big step compared to previous mechanisms. Even then, I suspect we will still have a special place for all those home-grown scripts for special needs.