Most of us with any kind of longevity in the Oracle world will remember the days when we wrote our own scripts to generate the DDL for a database object. We’d go hunting around in DBA_TABLES, DBA_TAB_COLUMNS, DBA_CONSTRAINTS etc to build a script that could be checked into source control or similar. Of course, as more and more features got added to the database, our home grown scripts got less and less reliable.
Thankfully, DBMS_METADATA came along to rescue us from all that tedium. It generates a comprehensive DDL for pretty much any database object you can throw at it. For example, here’s the trusty EMP table
SQL> select dbms_metadata.get_ddl('TABLE','EMP',user) from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP',USER)
------------------------------------------------------------------------------
CREATE TABLE "MCDONAC"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10) COLLATE "USING_NLS_COMP",
"JOB" VARCHAR2(9) COLLATE "USING_NLS_COMP",
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "MCDONAC"."DEPT" ("DEPTNO") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
The only “problem” with DBMS_METADATA is that when people see that resultant DDL, their response is often:
“Whoa there. That’s not the DDL I first entered when I created the table. What I see there looks waayyyyyy more complicated.”
To deal with that, there are some tweaks we can make to the session before calling DBMS_METADATA to get a less verbose output.
SQL> begin
2 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
3 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
4 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
5 dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', false);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl('TABLE','EMP',user) from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP',USER)
--------------------------------------------------------------------------------
CREATE TABLE "MCDONAC"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10) COLLATE "USING_NLS_COMP",
"JOB" VARCHAR2(9) COLLATE "USING_NLS_COMP",
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE,
CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "MCDONAC"."DEPT" ("DEPTNO") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
That is closer to what the original DDL probably looked like when it was run, but even with this cleanup, people have still been quick to point out two frustrations
- Hey, what’s with all the damn double quotes?!
- WHY ARE YOU SHOUTING AT ME?!
Unfortunately, there isn’t much you can do within the DBMS_METADATA API itself to deal with this, so I thought I’d write a little cleanup routine to help. My personal preferences are no double quotes and lowercase whenever possible, so of course I could write a very rudimentary script like:
SQL> select lower(replace(dbms_metadata.get_ddl('TABLE','EMP',user),'"')) from dual;
LOWER(REPLACE(DBMS_METADATA.GET_DDL('TABLE','EMP',USER),'"'))
--------------------------------------------------------------------------------
create table mcdonac.emp
( empno number(4,0) not null enable,
ename varchar2(10) collate using_nls_comp,
job varchar2(9) collate using_nls_comp,
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint emp_pk primary key (empno)
using index pctfree 10 initrans 2 maxtrans 255 compute statistics
tablespace users enable,
constraint emp_fk foreign key (deptno)
references mcdonac.dept (deptno) enable
) default collation using_nls_comp segment creation immediate
pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging
tablespace users ;
but that is a pretty risky option to take, because it could easily break my DDL generation. For example, consider this table.
SQL> create table people
2 ( pid number,
3 verified varchar2(1) default 'N',
4 gender varchar2(20)
5 check ( gender in ('Male','Female','Unspecified' ))
6 );
Table created.
SQL>
SQL> select lower(replace(dbms_metadata.get_ddl('TABLE','PEOPLE',user),'"')) from dual;
LOWER(REPLACE(DBMS_METADATA.GET_DDL('TABLE','PEOPLE',USER),'"'))
--------------------------------------------------------------------------------
create table mcdonac.people
( pid number,
verified varchar2(1) collate using_nls_comp default 'n',
gender varchar2(20) collate using_nls_comp,
check ( gender in ('male','female','unspecified' )) enable
) default collation using_nls_comp segment creation deferred
pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging
tablespace users ;
Whoops! I just broke my data rules, but even worse, the DDL is still valid from a syntax perspective so there will be nothing to alert me that the DDL is wrong until my application blows up in my face.
So I’ve built a little function which will sift through a DDL to clean it up as much as possible without breaking the definition. It achieves this by digging through the DDL one character at a time keeping tracking of single and double quotes that it encounters along the way. Here’s the function
SQL> set serverout on
SQL> create or replace
2 function ddl(p_type varchar2, p_name varchar2, p_owner varchar2 default user) return clob is
3
4 in_double boolean := false;
5 in_string boolean := false;
6 need_quotes boolean := false;
7
8 ddl clob;
9 idx int := 0;
10 res clob;
11 thischar varchar2(1);
12 prevchar varchar2(1);
13 nextchar varchar2(1);
14 sqt varchar2(1) := '''';
15 dqt varchar2(1) := '"';
16 last_dqt int;
17
18 procedure debug is
19 begin
20 dbms_output.put_line('---------------');
21 dbms_output.put_line('this ='||thischar);
22 dbms_output.put_line('last_dqt ='||last_dqt);
23 dbms_output.put_line('needsqt ='||case when need_quotes then 'true' else 'false' end);
24 dbms_output.put_line('RES ='||res);
25 dbms_output.put_line('prev ='||prevchar);
26 dbms_output.put_line('next ='||nextchar);
27 dbms_output.put_line('instring ='||case when in_string then 'true' else 'false' end);
28 dbms_output.put_line('indouble ='||case when in_double then 'true' else 'false' end);
29 end;
30
31 procedure append is
32 begin
33 if not need_quotes and not in_string and not in_double then
34 res := res || lower(thischar);
35 else
36 res := res || thischar;
37 end if;
38 end;
39 begin
40 dbms_lob.createtemporary(res,true);
41 dbms_lob.createtemporary(ddl,true);
42
43 select ltrim(dbms_metadata.get_ddl(upper(p_type),p_name,upper(p_owner)),chr(10)||chr(13)||' ')
44 into ddl
45 from dual;
46
47 loop
48 idx := idx + 1;
49 if idx > 1 then prevchar := thischar; end if;
50 thischar := substr(ddl,idx,1);
51 exit when thischar is null;
52 nextchar := substr(ddl,idx+1,1);
53
54 if thischar not in (dqt,sqt) then
55 append;
56 if in_double then
57 if thischar not between 'A' and 'Z' and thischar not between '0' and '9' and thischar not in ('$','#','_') or
58 ( prevchar = dqt and ( thischar in ('$','#','_') or thischar between '0' and '9' ) )
59 then
60 need_quotes := true;
61 end if;
62 end if;
63 elsif thischar = dqt and not in_double and not in_string then
64 append;
65 in_double := true;
66 need_quotes := false;
67 elsif thischar = dqt and in_double and not in_string then
68 last_dqt := instr(res,dqt,-1);
69 if last_dqt = 0 then
70 raise_application_error(-20000,'last_dqt died');
71 else
72 if not need_quotes then
73 res := substr(res,1,last_dqt-1)||lower(substr(res,last_dqt+1));
74 else
75 append;
76 end if;
77 need_quotes := false;
78 end if;
79 in_double := false;
80 elsif thischar = sqt then
81 append;
82 if not in_double then
83 if not in_string then
84 in_string := true;
85 else
86 if nextchar = sqt then
87 in_string := true;
88 res := res || nextchar;
89 idx := idx + 1;
90 else
91 in_string := false;
92 end if;
93 end if;
94 end if;
95 else
96 append;
97 end if;
98
99 end loop;
100 return res;
101 end;
102 /
Function created.
SQL>
SQL>
First I’ll give it a test against my simple EMP table
SQL> select ddl('table','EMP') from dual;
DDL('TABLE','EMP')
------------------------------------------------------------------------
create table mcdonac.emp
( empno number(4,0) not null enable,
ename varchar2(10) collate using_nls_comp,
job varchar2(9) collate using_nls_comp,
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint emp_pk primary key (empno)
using index pctfree 10 initrans 2 maxtrans 255 compute statistics
tablespace users enable,
constraint emp_fk foreign key (deptno)
references mcdonac.dept (deptno) enable
) default collation using_nls_comp segment creation immediate
pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging
tablespace users ;
But of course the real test comes for more complex DDL. Lets go nuts with some crazy definitions.
SQL> create table "T WITH SPACE" (
2 col1 int primary key,
3 "Col2" int unique,
4 "Col'$" date,
5 col5 xmltype not null,
6 col4 timestamp with time zone,
7 col10 int check ( col10 != 0 ),
8 col11 varchar2(10) check ( col11 != '"Hello"'),
9 col12 varchar2(10) check ( col12 != '"Hello'),
10 constraint tchk1 check ( col1||"Col2" != '"more quot''s"'),
11 constraint tchk2 check ( col1||"Col2" != q'{"x""""}')
12 );
Table created.
This table has mixed case names, multiple nested single and double quotes, and even has column names that contain quotes as well!
SQL> select ddl('table','T WITH SPACE') from dual;
DDL('TABLE','TWITHSPACE')
--------------------------------------------------------------------------------
create table mcdonac."T WITH SPACE"
( col1 number(*,0),
"Col2" number(*,0),
"Col'$" date,
col5 xmltype not null enable,
col4 timestamp (6) with time zone,
col10 number(*,0),
col11 varchar2(10) collate using_nls_comp,
col12 varchar2(10) collate using_nls_comp,
check ( col10 != 0 ) enable,
check ( col11 != '"Hello"') enable,
check ( col12 != '"Hello') enable,
constraint tchk1 check ( col1||"Col2" != '"more quot''s"') enable,
constraint tchk2 check ( col1||"Col2" != q'{"x""""}') enable,
primary key (col1)
using index pctfree 10 initrans 2 maxtrans 255
tablespace users enable,
unique ("Col2")
using index pctfree 10 initrans 2 maxtrans 255
tablespace users enable
) default collation using_nls_comp segment creation deferred
pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging
tablespace users
xmltype column col5 store as securefile binary xml (
tablespace users enable storage in row chunk 8192
nocache logging nocompress keep_duplicates ) allow nonschema disallow anyschema ;
SQL>
Voila!
Let me stress – I make no claim this this routine is 100% perfect for all object types. My main area of focus here was really just the obvious candidates – TABLES, INDEXES etc. If you find a example where it breaks, please send me the originating DDL and I’ll see if I can factor in those idiosyncrasies as well.
But hopefully, for most cases, you’ll get some use out of this routine. Source can be downloaded from my github repo. Enjoy!
Learning is not a spectator sport 
Leave a reply to Connor McDonald Cancel reply