How to make dbms_metadata.get_ddl more pretty/useful

Patryk Turowicz picture Patryk Turowicz · Nov 24, 2015 · Viewed 7.7k times · Source

I am creating a package to generate the DDL of objects in my schema (you parse the object name, and return a clob with DDL), so I can generate files, and put them straight into SVN.

I am using dbms_metadata.get_ddl, and it works great for all objects except tables/materialized views.

If I create a table as:

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;

And generate DDL with:

begin
   dbms_output.put_line(dbms_metadata.get_ddl( object_type => 'TABLE'
                                             , name => 'STACKOVERFLOW')
                        );
end;

It gives us:

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" 

To take all relevant indexes we can use:

begin
   dbms_output.put_line(dbms_metadata.get_dependent_ddl( object_type => 'INDEX'
                                                       , base_object_name => 'STACKOVERFLOW'));
end;

to have:

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" 

I want to create a file that contains: create table, constraints, indexes, grants (to have one file with all definition needed) and using dbms_metadata it looks impossible for me to do.

My problems with the output is:

  1. Double quotation of the name

  2. 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:

     dbms_metadata.SET_REMAP_PARAM(dbms_metadata.SESSION_TRANSFORM,'REMAP_SCHEMA','TEST_SCHEMA','');
    

    but you have to add like 8 more lines:

    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 => 'REMAP_SCHEMA', old_value => user, new_value => '');
    get_package_spec.ddl := DBMS_METADATA.FETCH_CLOB(hOpenOrig0);
    DBMS_METADATA.CLOSE(hOpenOrig0);
    
  3. 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 ui_test. Yes there is option to remove the constraints from get_ddl but we are loosing constraint/checks.

  4. How PL/SQL Developer creates the output

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

Does anybody know of a way to create output similar to PL/SQL Developer? I guess they created some XML parser of dbms_metadata.get_xml) to create a more pretty version (indents, order, all in good place, ready to compile anywhere).

Of course I can play with regexes or user_indexes but that's not the point.

ps. DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true); is a strange definition what's "pretty".

Answer

Arkadiusz Łukasiewicz picture Arkadiusz Łukasiewicz · Dec 30, 2015

dbms_metadata.get_dll gets oracle object as xml and next transform it by xslt to ddl script.

List of useful table select table_name from all_tables where table_name like 'META%'.

  1. METASTYLESHEET - maps a stylesheet to it's name

  2. METAXSL$ - maps an XMLTAG to the stylesheet name - link this to the 1st table

  3. METAVIEW$ - maps an object type to an XMLTAG - link this to ghe 2nd table
  4. METAXSLPARAM$ - lookup table for the transform filters available for each object type and transform type.

For a table oracle uses kutable for xml to ddl for a index oracle uses kuindex ... etc.

By setting parameters you can change behavior of transformation. To find useful parameter check METAXSLPARAM$ table or search it in style sheet documents. EMIT_SCHEMA - i have found in kucommon xslt

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA',false);  --undocumented remove schema
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_CREATION',false);  --undocumented remove segement creation
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',true);
select dbms_metadata.get_ddl( object_type => 'TABLE' , name => 'STACKOVERFLOW') from dual;