This discussion is archived
6 Replies Latest reply: Jan 7, 2013 12:51 PM by User286067 RSS

DBMS_METADTA API: How to Control Sorting of Returned CLOBs for a Schema

user6596050 Newbie
Currently Being Moderated
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');
DBMS_METADATA.SET_FILTER(schema_handle,'SCHEMA',schema_to_use);
DBMS_METADATA.SET_COUNT(schema_handle,1);
ddl_handle := DBMS_METADATA.ADD_TRANSFORM(schema_handle,'DDL');
dbms_metadata.set_transform_param(ddl_handle,'CONSTRAINTS_AS_ALTER',TRUE,'TABLE');
dbms_metadata.set_transform_param(ddl_handle,'SQLTERMINATOR',TRUE);

LOOP
object_definition := DBMS_METADATA.FETCH_CLOB(schema_handle);
EXIT WHEN object_definition IS NULL;
...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points