title: How to make dbms_metadata.get_ddl more pretty/useful
body: <p>I am creating a package to generate the DDL of objects in my schema (you parse the object name, and return a <code>clob</code> with DDL), so I can generate files, and put them straight into SVN.</p>
  
  <p>I am using <code>dbms_metadata.get_ddl</code>, and it works great for all objects except tables/materialized views.</p>
  
  <p>If I create a table as:</p>
  
  <pre><code>create table stackoverflow
       ( col_1 varchar2(64)
       , col_2 number
       , col_3 date);
  
  create index idx_test on stackoverflow(col_1);
  
  alter table stackoverflow add constraint ui_test unique (col_2) using index;
  </code></pre>
  
  <p>And generate DDL with:</p>
  
  <pre><code>begin
     dbms_output.put_line(dbms_metadata.get_ddl( object_type =&gt; 'TABLE'
                                               , name =&gt; 'STACKOVERFLOW')
                          );
  end;
  </code></pre>
  
  <p>It gives us:</p>
  
  <pre><code>CREATE TABLE "TEST_SCHEMA"."STACKOVERFLOW" 
   (  "COL_1" VARCHAR2(64), 
    "COL_2" NUMBER, 
    "COL_3" DATE, 
     CONSTRAINT "UI_TEST" UNIQUE ("COL_2")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS_DATA_TS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS_DATA_TS" 
  </code></pre>
  
  <p>To take all relevant indexes we can use:</p>
  
  <pre><code>begin
     dbms_output.put_line(dbms_metadata.get_dependent_ddl( object_type =&gt; 'INDEX'
                                                         , base_object_name =&gt; 'STACKOVERFLOW'));
  end;
  </code></pre>
  
  <p>to have:</p>
  
  <pre><code>CREATE INDEX "TEST_SCHEMA"."IDX_TEST" ON "MF"."STACKOVERFLOW" ("COL_1") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS_DATA_TS" 
  CREATE UNIQUE INDEX "TEST_SCHEMA"."UI_TEST" ON "MF"."STACKOVERFLOW" ("COL_2") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS_DATA_TS" 
  </code></pre>
  
  <p>I want to create a file that contains: create table, constraints, indexes, grants (to have one file with all definition needed) and using <code>dbms_metadata</code> it looks impossible for me to do.</p>
  
  <p>My problems with the output is:</p>
  
  <ol>
  <li><p>Double quotation of the name</p></li>
  <li><p>Schema name inside the DDL make us difficult to compile the same DDL across many schemas. To fix that we need to create some for of regex or adding something like the following can fix that:</p>
  
  <pre><code> dbms_metadata.SET_REMAP_PARAM(dbms_metadata.SESSION_TRANSFORM,'REMAP_SCHEMA','TEST_SCHEMA','');
  </code></pre>
  
  <p>but you have to add like 8 more lines:</p>
  
  <pre><code>hOpenOrig0 := DBMS_METADATA.OPEN('TABLE');
  DBMS_METADATA.SET_FILTER(hOpenOrig0,'NAME',p_object_name);
  DBMS_METADATA.SET_FILTER(hOpenOrig0,'SCHEMA',get_table.owner);
  tr := dbms_metadata.add_transform(hOpenOrig0, 'MODIFY');
  hTransDDL := DBMS_METADATA.ADD_TRANSFORM(hOpenOrig0,'DDL');
  dbms_metadata.set_remap_param(tr, name =&gt; 'REMAP_SCHEMA', old_value =&gt; user, new_value =&gt; '');
  get_package_spec.ddl := DBMS_METADATA.FETCH_CLOB(hOpenOrig0);
  DBMS_METADATA.CLOSE(hOpenOrig0);
  </code></pre></li>
  <li><p>There is no way of extracting constraints (that use indexes) and indexes at the same moment. You can't concatenate the output because of repeating definition of <code>ui_test</code>. Yes there is option to remove the constraints from <code>get_ddl</code> but we are loosing constraint/checks.</p></li>
  <li><p>How PL/SQL Developer creates the output</p>
  
  <pre><code>-- Create table
  create table STACKOVERFLOW
  (
    col_1 VARCHAR2(64),
    col_2 NUMBER,
    col_3 DATE
  )
  tablespace USERS_DATA_TS
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 128K
      next 128K
      minextents 1
      maxextents unlimited
      pctincrease 0
    );
  -- Create/Recreate indexes 
  create index IDX_TEST on STACKOVERFLOW (COL_1)
    tablespace USERS_DATA_TS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 128K
      next 128K
      minextents 1
      maxextents unlimited
      pctincrease 0
    );
  -- Create/Recreate primary, unique and foreign key constraints 
  alter table STACKOVERFLOW
    add constraint UI_TEST unique (COL_2)
    using index 
    tablespace USERS_DATA_TS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 128K
      next 128K
      minextents 1
      maxextents unlimited
      pctincrease 0
    );
  </code></pre></li>
  </ol>
  
  <p>Does anybody know of a way to create output similar to PL/SQL Developer? I guess they created some XML parser of <code>dbms_metadata.get_xml</code>) to create a more pretty version (indents, order, all in good place, ready to compile anywhere).</p>
  
  <p>Of course I can play with regexes or <code>user_indexes</code> but that's not the point.</p>
  
  <p>ps. <code>DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);</code> is a strange definition what's "pretty".</p>