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

Configuring Your Generated DDL in SQL Developer and SQLcl

3 Mins Read

You want the create script for a table.

AND you want the create script for ALL of your tables.

BUT also I want want the tablespace.

Forget the constraints.

How do you configure this in our tools?

SQL Developer: The Preferences & the Wizards

Go to the preferences.

Database, utilities, export.

This will also set the defaults for the Database Export wizard.
This will also set the defaults for the Database Export wizard.

SQLcl: SET DDL

HELP SET DDL

SET DDL

Might need to add more options, let us know!
Might need to add more options, let us know!

And now run the DDL command.

You could add these to your login.sql or do up an alias command to toggle things on and off...
You could add these to your login.sql or do up an alias command to toggle things on and off…
HR@orcl >help set ddl
SET DDL
  SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
          CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
          SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
          BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
         ]| OFF ]

HR@orcl >set ddl constraints off
DDL Option CONSTRAINTS off
HR@orcl >set ddl ref_constraints off
DDL Option REF_CONSTRAINTS off
HR@orcl >set ddl tablespace off
DDL Option TABLESPACE off
HR@orcl >set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES off
HR@orcl >ddl locations
  CREATE TABLE "HR"."LOCATIONS" 
   (	"LOCATION_ID" NUMBER(4,0), 
	"STREET_ADDRESS" VARCHAR2(40), 
	"POSTAL_CODE" VARCHAR2(12), 
	"CITY" VARCHAR2(30), 
	"STATE_PROVINCE" VARCHAR2(25), 
	"COUNTRY_ID" CHAR(2)
   ) ;
   COMMENT ON COLUMN "HR"."LOCATIONS"."LOCATION_ID" IS 'Primary key of locations table';
   COMMENT ON COLUMN "HR"."LOCATIONS"."STREET_ADDRESS" IS 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';
   COMMENT ON COLUMN "HR"."LOCATIONS"."POSTAL_CODE" IS 'Postal code of the location of an office, warehouse, or production site
of a company. ';
   COMMENT ON COLUMN "HR"."LOCATIONS"."CITY" IS 'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';
   COMMENT ON COLUMN "HR"."LOCATIONS"."STATE_PROVINCE" IS 'State or Province where an office, warehouse, or production site of a
company is located.';
   COMMENT ON COLUMN "HR"."LOCATIONS"."COUNTRY_ID" IS 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';
   COMMENT ON TABLE "HR"."LOCATIONS"  IS 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables. ';
  CREATE INDEX "HR"."LOC_CITY_IX" ON "HR"."LOCATIONS" ("CITY") 
  ;
  CREATE INDEX "HR"."LOC_COUNTRY_IX" ON "HR"."LOCATIONS" ("COUNTRY_ID") 
  ;
  CREATE INDEX "HR"."LOC_STATE_PROVINCE_IX" ON "HR"."LOCATIONS" ("STATE_PROVINCE") 
  ;
HR@orcl >

Don’t want the quoted identifiers?

Use the Data Modeler.

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

5 Comments

  1. Lee Reply

    Currently using Oracle SQL Developer 21.4.3
    Why is it not possible to generate DDL with the Database Export that contains the NOT NULL constraints within the CREATE TABLE scripts.
    The DDL Preview when viewing the table shows CREATE TABLE including NOT NULL and for example, indexes are in the ALTER TABLE.
    The included Data Modeler can also do this, but I prefer the Database Export as it can save the scripts per type, whereas the modeler saves a single file or multiple files, there is no type possibility.

    • thatjeffsmith Post Author

      What you’re seeing is how database engine itself sees your table, it expresses the DDL the way we print it. We just ask the DB for your DDL.

      Here are your options –

      SQL> help set ddl
      SET DDL
      SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
      CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
      SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
      BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
      ] | ON | OFF ]
      SQL>

  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
    But this fails as all users in dba_users are with EDITIONS_ENABLED=’N’

    I just opened SR SR 3-14571041491 : sqldeveloper export ddl adds “editionable” to all packages
    and they says those 2 bugs are closed as not a bug!!!
    It means export DDL of sqldeveloper is UNUSABLE for 12c!!!
    and it looks like nobody cares!!!!

  3. Zach Reply

    Is there a list somewhere of the object types that the ddl command supports?

    I saw somewhere that it is using dbms_metadata.get_ddl behind the scenes, but attempting to run the command below fails.

    ddl job_name PROCOBJ

    but

    dbms_metadata.get_ddl(‘PROCOBJ’, ‘JOB_NAME’)

    works just fine

  4. Alex C Reply

    Hello Jeff.
    I would like to give a try to sqlcl, but one thing confuses me.
    I have the latest SQLDeveloper package installed (named ‘Windows 64-bit with JDK 8 included’)
    But sqlcl is not mapped to this ‘internal’ JDK in any way and asks me to locate another one (JDK7 as I remember)
    Can it be improved out the box?
    Such inconsistency is weird and I don’t want to waste my system with multiple JDK instances

Write A Comment