Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Generating Table DDL via SQLcl, with and without Storage Clauses

2 Mins Read

I don’t like deleting blog posts just because they’re old, or no longer true, but this one came before we made this SO MUCH SIMPLER. Please read this post instead.

TL/DR? You can now just say SET DDL STORAGE OFF if you want to set the metadata transform bits 🙂

We’re getting much closer to having Oracle SQL Developer version 4.1 ready for release – which includes SQLcl, our new command-line interface for Oracle Database.

Just a quick reminder in case you’re too lazy to click to that other post:

We’ve taken our existing Java based SQL*Plus script engine from SQL Developer, and built a new command line interface that does most of what SQL*Plus offers plus adds a bunch of cool GUI features from SQL Developer. We’re calling this new program, SQLcl.

One of the new commands is DDL.

 
SQL> ddl hr.employees
 
  CREATE TABLE "HR"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) 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 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  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
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 
   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")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"
 
  CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"
 
  CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"
 
  CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"
 
  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"."SECURE_EMPLOYEES"
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE

Woohoo! But I don’t need that icky STORAGE stuff

So turn it off.

We’re calling DBMS_METADATA under the covers to generate this DDL. And you can tell that package exactly how you want your DDL generated. The command is pretty simple, but it’s a bit verbose for me to type, much less to remember.

So I created my own command to toggle this on and off. Well, two commands to be honest.

How do you create your own ‘commands?’ Use the ALIAS feature.

ALIAS new_commmand=code

You can feed one or more parameters to your command, and you can execute one or more statements. Mine are very simple.

Now I can just run this new command to turn storage clauses off for my DDL.
Now I can just run this new command to turn storage clauses off for my DDL.
SQL> dd_storage_off
Command=dd_storage_off
 
PL/SQL PROCEDURE successfully completed.
 
SQL> ddl hr.employees
 
  CREATE TABLE "HR"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) 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_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  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
   )
 
   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 are
   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
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 departm
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 "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"."SECURE_EMPLOYEES"
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE
SQL>

You can find all of the TRANSFORM procedure options here for defining how you want your DDL generated.

And of COURSE we support spool, so if I want a script file of a table without storage parameters, I just run…

dd_storage_off
spool c:\scripts\employeees_no_storage.sql
ddl hr.employees
spool off
dd_storage_on
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

9 Comments

  1. Ramon Reply

    Hi,
    Is it possible to execute DDL dynamically?

    select ‘ddl ‘||view_name||’ save ‘||view_name||’.sql’ cmd from all_views and have them execute somehow.

    is it also possible to execute sqlcl without entering inside sqlcl may be by passing argument
    something like
    sqlcl <<EOF
    ddl some_pkg save save_location
    <<EOF
    exit;

    Could not find anywhere regarding this if you could point

  2. Rivka Reply

    editionable bug:
    When I export packages and procedures from a 12c database, using SQL Developer, the default statement is
    CREATE OR REPLACE EDITIONABLE PROCEDURE.
    it says EDITIONABLE even though all users in dba_users are with EDITIONS_ENABLED=’N’
    This fails when run.
    So export DDL is no longer usable!!!

    • thatjeffsmith Post Author

      We are making a call to a DBMS package..The database is generating the DDL, not SQLDev. It’s possible were making the PKG call incorrectly or the DB isn’t generating the code correctly.

      If you use data pump to do an export, does it include the exitionable syntax?

  3. Günter Reply

    Hello Jeff,
    thanks for the post – i use this method also inside SQL Developer, but there is one difference.

    execute DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle=>DBMS_METADATA.SESSION_TRANSFORM,name=>’EMIT_SCHEMA’,value=>false)

    works in SQLcl but not in SQL Developer – any ideas ?

    Günter

    • Günter

      The GUI (SQL-Tab), if i use

      SELECT DBMS_METADATA.GET_DDL (‘TABLE’, ‘my_table_name’)FROM DUAL;

      it works.

    • thatjeffsmith Post Author

      the SQL tab is controlled by the Preferences. Tools > Preferences > … Export – uncheck anything you don’t want.

    • Günter

      Nice would be the full options as in Tools>Preferences>Database>Utilities>Difference. Not only one option STORAGE instead more detailed PARTITIONING, TABLESPACE …

Write A Comment