How to generate a DDL and a DML in Oracle SQL (Scriptable)

Question

I want to generate a .DDL that contains all the 'CREATE TABLES' and a .DML that contains all the 'INSERT'

I tried to use the answers of this question but didn't get to modify them to get the DDL and the DML

score 3 · Answer 1 · answered Apr 05 '22 at 08:16

The question you referenced lets you get the DDL, to get the DML its easiest to use a tool. SQLcl is free and lets you do it easily, eg

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/DEC/80        800                    20
      7499 ALLEN      SALESMAN        7698 20/FEB/81       1600        300         30
      7521 WARD       SALESMAN        7698 22/FEB/81       1250        500         30
      7566 JONES      MANAGER         7839 02/APR/81       2975                    20
...

14 rows selected.

SQL> select /*insert*/ * from scott.emp;
REM INSERTING into SCOTT.EMP
SET DEFINE OFF;
Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17/DEC/80','DD/MON/RR HH12:MI:SSXFF AM'),800,null,20);
Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20/FEB/81','DD/MON/RR HH12:MI:SSXFF AM'),1600,300,30);
Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22/FEB/81','DD/MON/RR HH12:MI:SSXFF AM'),1250,500,30);
Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02/APR/81','DD/MON/RR HH12:MI:SSXFF AM'),2975,null,20);
Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28/SEP/81','DD/MON/RR HH12:MI:SSXFF AM'),1250,1400,30);
Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01/MAY/81','DD/MON/RR HH12:MI:SSXFF AM'),2850,null,30);
...
...

Download SQLcl from Oracle

https://www.oracle.com/au/tools/downloads/sqlcl-downloads.html

`to_timestamp('17/DEC/80','DD/MON/RR HH12:MI:SSXFF AM')` It really auto-generates dates with an `RR` year and expects a time component but does not provide it? — MT0, Apr 05 '22 at 08:19
I think JDBC says everything is a timestamp, so thats the output you get. It works but looks a little odd I guess — Connor McDonald, Apr 05 '22 at 08:20
Out of curiosity (since I don't have SQLcl), does it give you a better format if you use `ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';` or `ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';` first? — MT0, Apr 05 '22 at 08:23

score 2 · Answer 2 · answered Apr 05 '22 at 21:16

Getting the DDL is easier now, it's a built-in command for SQL Developer and SQLcl.

For EMPLOYEES table, bonus: grabbing dependent objects on the table.

cd c:\users\jdsmith\desktop
set feedback off
set ddl segment_attributes off
set ddl storage off
set ddl tablespace off
spool employees_so.sql
ddl employees
set sqlformat insert
select * from employees;
spool off

Run this via F5 in SQL Developer (desktop) or SQLcl (cli at shell).

Out pops this file -

CREATE TABLE "HR"."EMPLOYEES" 
      ( "EMPLOYEE_ID" NUMBER(6,0), 
        "FIRST_NAME" VARCHAR2(20) COLLATE "USING_NLS_COMP", 
        "LAST_NAME" VARCHAR2(25) COLLATE "USING_NLS_COMP" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
        "EMAIL" VARCHAR2(25) COLLATE "USING_NLS_COMP" CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
        "PHONE_NUMBER" VARCHAR2(20) COLLATE "USING_NLS_COMP", 
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
        "JOB_ID" VARCHAR2(10) COLLATE "USING_NLS_COMP" CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
        "SALARY" NUMBER(8,2), 
        "COMMISSION_PCT" NUMBER(2,2), 
        "MANAGER_ID" NUMBER(6,0), 
        "DEPARTMENT_ID" NUMBER(4,0), 
         CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
      USING INDEX  ENABLE, 
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE, 
         CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE, 
         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
       )  DEFAULT COLLATION "USING_NLS_COMP" ;
      CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID") 
      ;
    ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
      USING INDEX "HR"."EMP_EMP_ID_PK"  ENABLE;
    
       COMMENT ON COLUMN "HR"."EMPLOYEES"."EMPLOYEE_ID" IS 'Primary key of employees table.';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."FIRST_NAME" IS 'First name of the employee. A not null column.';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."LAST_NAME" IS 'Last name of the employee. A not null column.';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."EMAIL" IS 'Email id of the employee';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."PHONE_NUMBER" IS 'Phone number of the employee; includes country code and area code';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."HIRE_DATE" IS 'Date when the employee started on this job. A not null column.';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."JOB_ID" IS 'Current job of the employee; foreign key to job_id column of the
    jobs table. A not null column.';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."SALARY" IS 'Monthly salary of the employee. Must be greater
    than zero (enforced by constraint emp_salary_min)';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."COMMISSION_PCT" IS 'Commission percentage of the employee; Only employees in sales
    department elgible for commission percentage';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."MANAGER_ID" IS 'Manager id of the employee; has same domain as manager_id in
    departments table. Foreign key to employee_id column of employees table.
    (useful for reflexive joins and CONNECT BY query)';
       COMMENT ON COLUMN "HR"."EMPLOYEES"."DEPARTMENT_ID" IS 'Department id where employee works; foreign key to department_id
    column of the departments table';
       COMMENT ON TABLE "HR"."EMPLOYEES"  IS 'employees table. Contains 107 rows. References with departments,
    jobs, job_history tables. Contains a self reference.';
    
      CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID") 
      ;
    
      CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID") 
      ;
    
      CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID") 
      ;
    
      CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME") 
      ;
    
      CREATE OR REPLACE EDITIONABLE TRIGGER "HRREST"."TRIG_FOREIGN_TABLE" 
    BEFORE INSERT ON HR.EMPLOYEES 
    REFERENCING OLD AS OLD NEW AS NEW 
    BEGIN
      NULL;
    END;
    /
    ALTER TRIGGER "HRREST"."TRIG_FOREIGN_TABLE" ENABLE;
    
      CREATE OR REPLACE EDITIONABLE TRIGGER "HR"."SECURE_EMPLOYEES" 
      BEFORE INSERT OR UPDATE OR DELETE ON employees
    BEGIN
      secure_dml;
    END secure_employees;
    /
    ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE;
    
      CREATE OR REPLACE EDITIONABLE TRIGGER "HR"."UPDATE_JOB_HISTORY" 
      AFTER UPDATE OF job_id, department_id ON employees
      FOR EACH ROW
    BEGIN
      add_job_history(:old.employee_id, :old.hire_date, sysdate,
                      :old.job_id, :old.department_id);
    END;
    /
    ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE;
    
      CREATE OR REPLACE EDITIONABLE TRIGGER "HR"."EMPLOYEES_EMPLOYEE_ID_TRG" 
    before insert on employees
    for each row
    begin
      if :new.employee_id is null then
        select employees_seq.nextval into :new.employee_id from sys.dual;
      end if;
    end;
    /
    ALTER TRIGGER "HR"."EMPLOYEES_EMPLOYEE_ID_TRG" ENABLE;
    REM INSERTING into EMPLOYEES
    SET DEFINE OFF;
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (100,'Steven','King','SKING','515.123.4567',to_date('18-06-1987 06:00:00','DD-MM-YYYY HH24:MI:SS'),'AD_PRES',240000,null,null,90);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',to_date('22-09-1989 06:00:00','DD-MM-YYYY HH24:MI:SS'),'AD_VP',17000,null,100,90);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (102,'Lex','De Haan','LDEHAAN','515.123.4569',to_date('14-01-1993 06:00:00','DD-MM-YYYY HH24:MI:SS'),'AD_VP',17000,null,100,90);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (103,'Alexander','Hunold','AHUNOLD','590.423.4567',to_date('03-01-1990 00:00:00','DD-MM-YYYY HH24:MI:SS'),'IT_PROG',9000,null,102,60);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (104,'Bruce','Ernst','BERNST','590.423.4568',to_date('20-05-1991 00:00:00','DD-MM-YYYY HH24:MI:SS'),'IT_PROG',6000,null,103,60);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (105,'David','Austin','DAUSTIN','590.423.4569',to_date('25-06-1997 00:00:00','DD-MM-YYYY HH24:MI:SS'),'IT_PROG',4800,null,103,60);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (106,'Valli','Pataballa','VPATABAL','590.423.4560',to_date('05-02-1998 00:00:00','DD-MM-YYYY HH24:MI:SS'),'IT_PROG',4800,null,103,60);
    Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (107,'Diana','Lorentz','DLORENTZ','590.423.5567',to_date('07-02-1999 00:00:00','DD-MM-YYYY HH24:MI:SS'),'IT_PROG',4200,null,103,60);
    ...

score 1 · Answer 3 · answered Apr 06 '22 at 13:11

To adapt @ThatJeffSmith's answer

If you have three tables: employees, departments, jobs then you can use:

cd c:\users\jdsmith\desktop
set feedback off
set ddl segment_attributes off
set ddl storage off
set ddl tablespace off
spool myschema.ddl
ddl employees
ddl departments
ddl jobs
spool off

spool myschema.dml
set sqlformat insert
select * from employees;
select * from departments;
select * from jobs;
spool off

How to generate a DDL and a DML in Oracle SQL (Scriptable)

3 Answers3

Linked