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
      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;
      ...