Systems Engineering and RDBMS

Generating DDL in Oracle

Posted by decipherinfosys on June 6, 2007

In one of the blog post before, we had talked about generating DDL in SQL Server.  In this one, we will mention how the same thing can be done in Oracle and in the future we will cover the same for DB2 LUW as well.  There are a couple of options available in Oracle to generate the DDL:

exp/imp

Using export/import command (normally used for taking backup) can be used to extract the DDL. For that first we need to export the objects into the file.  You can use your own credentials and table names for testing purpose.

exp userId=decipher/decipher@orcl file=exp.dmp TABLES=(‘TEST_CASE’,’TEST_CASE_SRL’) ROWS=N

Here we are exporting tables TEST_CASE and TEST_CASE_SRL. We are only interested in structure so we are not exporting any rows. File exp.dmp will be created in the directory from which exp command is executed.

Now during import, we can use indexfile clause to extract out the DDL for the exported object.

imp userID=decipher/decipher@orcl indexfile=ddl.sql file=exp.dmp FULL=Y

In above example, we are not importing data into any schema, instead we are creating file which contains the DDL information of the exported object. File will be created in the same folder from which imp command is executed. For clarity and brevity of the document, we are not pasting the output of ddl.sql file here. But it should contain table definition along with index and constraint information. Except ‘Create Index’ statement, every thing else will be preceded with REM (comments for SQL*Plus session).  We can remove the REM using any text editor.

dbms_metadata.get_ddl

Starting 9i, Oracle introduced new package dbms_metadata. It contains some of the very useful procedures and functions. One of such functions is get_ddl. Upon execution of this function, it spits out the DDL for a given table. Here is the declaration of the function. Only caveat here is, it returns definition of single object.

DBMS_METADATA.GET_DDL
(
object_type    IN VARCHAR2,
name         IN VARCHAR2,
schema         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT ‘COMPATIBLE’,
model         IN VARCHAR2 DEFAULT ‘ORACLE’,
transform         IN VARCHAR2 DEFAULT ‘DDL’
)
Following is command followed by output. Output is formatted little for clarity. Since it returns CLOB, make sure that SQL*Plus environment values are set correctly.

SQL> SET heading off
SQL> SET long 10000
SQL> SET pages 100
SQL> SELECT dbms_metadata.get_ddl(‘TABLE’,’TEST_CASE_SRL’)
FROM DUAL;

And output is,

CREATE TABLE “DECIPHER”.”TEST_CASE_SRL”
(
“SRL_NBR”         VARCHAR2(15) NOT NULL ENABLE,
“CASE_NBR”        VARCHAR2(10),
“CASE_SEQ_NBR”     NUMBER(5,0),
“SRL_QTY”         NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
CONSTRAINT “PK_TEST_CASE_SRL” PRIMARY KEY (“SRL_NBR”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS”  ENABLE,
CONSTRAINT “FK_TEST_CASE_SRL_TO_TEST_CASE” FOREIGN KEY (“CASE_NBR”,
“CASE_SEQ_NBR”)
REFERENCES “DECIPHER”.”TEST_CASE” (“CASE_NBR”, “CASE_SEQ_NBR”)
ENABLE
)
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
)
TABLESPACE “USERS”

We do have an index on the table, but it didn’t generate index associated with the table. To extract the indexes, we have to fire a separate get_ddl command.  The command is,

SQL> SELECT dbms_metadata.get_ddl(‘INDEX’,’FK_TEST_CASE_SRL_TO_TEST_CASE’)
FROM DUAL;

Output is,

CREATE INDEX “DECPHER”.”FK_TEST_CASE_SRL_TO_TEST_CASE”
ON “DECIPHER”.”TEST_CASE_SRL” (“CASE_NBR”, “CASE_SEQ_NBR”)
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS”

SQL generated by any of the above method, can be used without any further changes. We can also write our own stored procedure to go against the meta-data tables and can prepare well formatted file but both of these approaches are very handy.

One Response to “Generating DDL in Oracle”

  1. […] tableRandom Number GenerationPadding Strings with charactersWSS 3.0 and SQL Server Embedded EditionGenerating DDL in OracleCase insensitive behavior in Oracle « […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: