Getting DDL for objects with sdsql

Getting ddl out for any object is quite simple.  You can just call dbms_metadata with something nice and easy like select dbms_metata.get_ddl('TABLE','EMP') from dual;

SQL>  select dbms_metadata.get_ddl('TABLE','EMP') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "KLRICE"."EMP"
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
 "ENA

SQL>

Now you are all set and go create a table right ? Not really. The easiest part is setting pages or set long to get the full output.  The hard part is getting the dependent objects like triggers.


Now introducing the new DDL command

KLRICE@ORCL>ddl emp

  CREATE TABLE "KLRICE"."EMP" 
   ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "MYCLOB" CLOB, 
  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 "SYSTEM"  ENABLE, 
  FOREIGN KEY ("MGR")
   REFERENCES "KLRICE"."EMP" ("EMPNO") ENABLE, 
  FOREIGN KEY ("DEPTNO")
   REFERENCES "KLRICE"."DEPT" ("DEPTNO") ENABLE
   ) 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 "SYSTEM" 
 LOB ("MYCLOB") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE 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)) 

  CREATE OR REPLACE EDITIONABLE TRIGGER "KLRICE"."EMP_TRG1" 
before insert on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from dual;
   end if;
end;


ALTER TRIGGER "KLRICE"."EMP_TRG1" ENABLE
KLRICE@ORCL>

Too much details?

This is still using dbms_metadata underneath it so you can adjust the output by setting some transformation parameters on metadata.  For example:

KLRICE@ORCL>host cat examples/ddl_emp.sql
begin
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

  end;
/

ddl emp
KLRICE@ORCL>@examples/ddl_emp.sql
anonymous block completed

  CREATE TABLE "KLRICE"."EMP" 
   ( "EMPNO" NUMBER(4,0), 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "MYCLOB" CLOB
   ) ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "KLRICE"."EMP_TRG1" 
before insert on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from dual;
   end if;
end;


/
ALTER TRIGGER "KLRICE"."EMP_TRG1" ENABLE;
KLRICE@ORCL>

But wait I have an Application Express Application I need the 'ddl' for that too. Give this a try.

OBE@ORCL>apex export 100
<<< LOTS AND LOTS OF TEXT WILL PRINT >>

Now couple these with spool and you have a sql script to get objects with dependencies and the apex application that goes with it all.

OBE@ORCL>spool mybuild.sql
OBE@ORCL>ddl emp
OBE@ORCL>ddl dept
OBE@ORCL>apex export 100
OBE@ORCL>spool off