How to make dbms_metadata.get_ddl more pretty/useful

Question

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 1

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;