Configuring Your Generated DDL in SQL Developer and SQLcl

thatjeffsmith SQL Developer 2 Comments

Tell Others About This Story:

You want the create script for a table.

You want the create script for ALL of your tables.

You want the tablespace.

But not 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…

[email protected]? >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 ]
 
[email protected]? >SET ddl constraints off
DDL OPTION CONSTRAINTS off
[email protected]? >SET ddl ref_constraints off
DDL OPTION REF_CONSTRAINTS off
[email protected]? >SET ddl tablespace off
DDL OPTION TABLESPACE off
[email protected]? >SET ddl segment_attributes off
DDL OPTION SEGMENT_ATTRIBUTES off
[email protected]? >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") 
  ;
[email protected]? >
Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 2

  1. 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

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *