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:
Double quotation of the name
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);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 fromget_ddlbut we are loosing constraint/checks.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".