Oracle SQL Developer: Configuring Object DDL Scripts

thatjeffsmith SQL Developer 30 Comments

Tell Others About This Story:

One of the more popular features in SQL Developer is the ability to generate the CREATE DDL script for any object. If you open an object editor, the SQL page shows this script.

You'll see this 'SQL' page for each type of database object editor in SQL Developer

You’ll see this ‘SQL’ page for each type of database object editor in SQL Developer

Now, what many of you don’t realize is that the way this code can be generated is configurable.

The requests I am most frequently asked to demo here are:

  1. No storage parameters
  2. No schema notation
  3. Show me the grants!

Actually, there’s another request that I won’t go into detail here, but the quoted identifiers when they are not necessary…that’s also on MY wish list 🙂

How to Configure the DDL Generation

Open the preferences. Go to the database page, then the utilities. And then, go to the Export page.

From there, the very top of that preference panel specifies how the DDL will be generated when you use features like the object editors, the Export wizard, and the Cart.

Grants aren't enabled by default.

Grants aren’t enabled by default.

Once I make my changes, I can just refresh my SQL page on my table editor.

And voila, my code is ready:

 
  CREATE TABLE EMPLOYEES 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20 BYTE), 
	LAST_NAME VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE, 
	EMAIL VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL ENABLE, 
	PHONE_NUMBER VARCHAR2(20 BYTE), 
	HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL ENABLE, 
	JOB_ID VARCHAR2(10 BYTE) 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) ENABLE, 
	 CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID) ENABLE, 
	 CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID)
	  REFERENCES DEPARTMENTS (DEPARTMENT_ID) ENABLE, 
	 CONSTRAINT EMP_JOB_FK FOREIGN KEY (JOB_ID)
	  REFERENCES JOBS (JOB_ID) ENABLE, 
	 CONSTRAINT EMP_MANAGER_FK FOREIGN KEY (MANAGER_ID)
	  REFERENCES EMPLOYEES (EMPLOYEE_ID) ENABLE
   ) ;
 
   COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS 'Primary key of employees table.';
   COMMENT ON COLUMN EMPLOYEES.FIRST_NAME IS 'First name of the employee. A not null column.';
   COMMENT ON COLUMN EMPLOYEES.LAST_NAME IS 'Last name of the employee. A not null column.';
   COMMENT ON COLUMN EMPLOYEES.EMAIL IS 'Email id of the employee';
   COMMENT ON COLUMN EMPLOYEES.PHONE_NUMBER IS 'Phone number of the employee; includes country code and area code';
   COMMENT ON COLUMN EMPLOYEES.HIRE_DATE IS 'Date when the employee started on this job. A not null column.';
   COMMENT ON COLUMN 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 EMPLOYEES.SALARY IS 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)';
   COMMENT ON COLUMN EMPLOYEES.COMMISSION_PCT IS 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage';
   COMMENT ON COLUMN 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 EMPLOYEES.DEPARTMENT_ID IS 'Department id where employee works; foreign key to department_id
column of the departments table';
   COMMENT ON TABLE EMPLOYEES  IS 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.';
 
  CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES (DEPARTMENT_ID) 
  ;
 
  CREATE INDEX EMP_JOB_IX ON EMPLOYEES (JOB_ID) 
  ;
 
  CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES (MANAGER_ID) 
  ;
 
  CREATE INDEX EMP_NAME_IX ON EMPLOYEES (LAST_NAME, FIRST_NAME) 
  ;
 
  CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES 
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/
ALTER TRIGGER SECURE_EMPLOYEES DISABLE;
 
  CREATE OR REPLACE TRIGGER 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 UPDATE_JOB_HISTORY ENABLE;
 
 
  GRANT SELECT ON EMPLOYEES TO DOES_NOT_OWN_ANYTHING;
  GRANT SELECT ON EMPLOYEES TO LOW_PRIVS;
  GRANT REFERENCES ON EMPLOYEES TO OE;
  GRANT SELECT ON EMPLOYEES TO OE;

But I Need Even More Options

Then might I suggest the Data Modeler?

Add your object(s) to a relational model, and then generate the DDL.

You have a few more options here. And you can specify if you want those darn quoted identifiers or not, e.g. CREATE OR REPLACE TABLE X vs CREATE OR REPLACE TABLE “X”.

More options!

More options!

One last thing.

If you don’t open the Physical model, the DDL generated won’t include the information from there. For example the triggers and tablespaces won’t be there. So you have even more flexibility.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 30

  1. Any way to stop the ddl generation to not use double quotes for the column names? like the ones listed below:

    , “DAY_OF_MONTH” NUMBER
    , “HOUR” NUMBER
    , “MINUTE” NUMBER
    , “LAST_RUN_DATETIME” DATE

    1. thatjeffsmith Post
      Author

      Nope, and believe me I’ve tried.

      If you use the data modeler to generate your ddl, it won’t be quoted unless it needs to be.

      But in SQLDev proper, we use DBMS_METADATA to generate the DDL and it quotes everything.

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        I understand that DDL is generated by a database (presumably by dbms_metadata) and nothing can be done to remove double quotes. I also understand that the behaviour can be controlled by ‘Utilities: Export’ options. What I don’t understand though, what generates DDL in Edit Table -> DDL -> SQL Statement -> Create), as it seems to be different. It doesn’t look like it is controlled by ‘Export’ or ‘Data Modeller: DDL’ options. Also, why find text option is disabled in SQL tab? I am using 4.1.5 version.

        1. thatjeffsmith Post
          Author

          >>what generates DDL in Edit Table
          SQL Dev does based on what the user is clicking in the dialog.

          The create/alter dialogs actually come from JDeveloper, although we ultimately own them.

  2. Is it possible to add a comment above each unique table like below.
    /*=======================================================*/
    /* Table: table_name */
    /*=======================================================*/

    Currently SQL Developer DDL preview adds this at the top automatically:
    — Generated by Oracle SQL Developer Data Modeler 4.1.1.888
    — at: 2015-08-27 13:06:04 CDT
    — site: Oracle Database 11g
    — type: Oracle Database 11g

    1. thatjeffsmith Post
      Author
  3. Hi Jeff
    ignoring the entire storage definition for a table might not be the best solution as pctfree and other parameters are important. So is there a way to uncheck only “old” parameters like pctused, pctincreate etc.?

    1. thatjeffsmith Post
      Author

      I think it’s all or nothing at this point, sounds like a good ER though

      You have way more flexibility when generating physical parameters in your script in the Data Modeler though.

  4. FWIW I downloaded 4.1 and tried again. Same issue. It attempts to drop and re-create the PK. I stumbled across transformation rules under [Tools]–>[Data Modeler]–>[Design Rules]–>[Transformations] and thought I hit the jackpot when I saw something to the effect of “index drop/re-create on FK” but deleting it had no effect. (Not too surprised – this is related to ERD creation, right?) Oh well – I was hoping it was something obvious. I’m fine copying and pasting the relevant code to do what I need.

  5. When editing a table, is there a way to tell SQL Developer NOT to automatically try to drop and recreate an index?
    When I want to do something as simple as enabling or disabling a constraint, SQL Developer wants to drop the index sitting on my primary key. Of course, Oracle won’t do this (ORA-02429: cannot drop index used for enforcement of unique/primary key), so I end up copying the code I need from DDL and running it in a separate editor window.

    1. thatjeffsmith Post
      Author

      where are you editing the table, inside a data modeler design, or in a alter table dialog in the sql developer tree? tell me exactly how you’re getting to the ‘disable constraint’ bit – there’s more than one in the tool.

      1. Sure! Here’s a step-by-step just to cover all the bases:
        Create a table TEST1 with an ID column NUMBER(10) as PK.
        Create another table TEST2 with an ID column NUMBER(10) as PK and a column TEST1_ID NUMBER(10). TEST1_ID will have a foreign constraint on the TEST1 table, ID column, but don’t create that relationship yet. Just save the tables.
        Now, in the Connections tab, right click on TEST2 and click [Edit]. Click [Constraints], then click the green [+], and [New Foreign Key Constraint]. Set the following values:
        Table: TEST1,
        Constraint: TEST1_PK,
        Local Column: TEST1_ID,
        Referenced Column: ID.
        *Don’t* click [OK].
        At this point, check the [DDL] and you’ll see: DROP INDEX SA.TEST2_PK; CREATE UNIQUE INDEX TEST2_PK… and piles of other code defining storage, buffer_pool..etc before the simple alter statement I actually need.

        1. thatjeffsmith Post
          Author

          Weird…in version 4.1 I just get this
          alter table “HR”.”TEST2″ add constraint xfk_test1_col1 foreign key(“COLUMN2”) references “TEST1″(“COLUMN1”)

          which seems to be what you want

  6. Hi Jeff

    Is there a way to find text in the “SQL” tab?? For me this is a key feature that is missing in SQL Developer ….

    And perhaps a way to drilldown to a object? The equivalent of F4 in Toad?

    Thx for the assistance.

    Rgds
    T

    1. thatjeffsmith Post
      Author
      1. Awesome thx Jeff.

        I see this works like a charm in a SQL query window. Wasn’t aware of this feature.

        I see however when you’re in the SQL tab on the object editor this feature doesn’t work (not shift+F4 or ctrl-hover)? Also CTRL+F does not work in this tab?

        Any ideas for searching for text and drilling down in this SQL tab?

        We have quite big complicated views and it can become quite irritating if you have to copy the sql out the whole time… 🙂

        For me this is one of the biggest shortcomings in SQLDeveloper at the moment.

        Maybe an idea for a feature in a next release.

        Thx a lot for the assistance.

        Rgds

  7. I’ve been using SQL Developer since version 2.0, but we’re now on version 4.0.2. So, I set several of my preferences in the distant past and just keep importing them. The issue is, when I create DDL scripts using the right-click menu, I get all triggers included (even instead of triggers). Our new hires who are starting on version 4.0.2 do not, and I can’t find the setting to enable this functionality. It’s not on the option screen you have displayed above.

  8. Jeff,

    Any plans to allow scripting of “database export” jobs OR the ability to store the selections as a template and be recalled so they can be repeated over and over? If so, then the ability to include, for example, procedures (objects) with name selections using wildcards (% and _ ) would be extremely useful. ie. all procedures LIKE ‘ZZ_%’.

    Chris

    1. thatjeffsmith Post
      Author

      You can script database exports now. The SQL Developer command-line interface (SDCLI) supports Database Export and the Cart feature for generating DDL. Doesn’t support wildcarding though.

  9. Jeff,

    Any idea on my Q?
    I need to remove the schema within the FROM clause, how I do this? If I uncheck the “Include schema”, it removes the schema in the CREATE clause. ANy idea?

    Efren

  10. Hi,
    Please send a query to below scenario.once again thanks.

    “Send an email to an email ID from Oracle whenever an employee’s salary is changed”

    Thanks,
    Manoj

    1. thatjeffsmith Post
      Author
  11. I need to remove the schema within the FROM clause, how I do this? If I uncheck the “Include schema”, it removes the schema in the CREATE clause. ANy idea?

    1. thatjeffsmith Post
      Author

      If you have a view, say

      CREATE OR REPLACE VIEW X…select * from scott.emp…

      There’s no way for us to remove that scott. in the FROM clause – that text is there b/c you put it there. It’s in the ‘source’

      If you look at ALL_SOURCE you can see that the actual create or replace isn’t stored in the database for stored procedures – hence the flexibility for generating the DDL.

      I’m just guessing at what you’re going for – give me a code sample and I can give you a better answer. Or call me. I’m in Aria.

  12. Great timing! Getting caught up on your recent posts – the preference changes in Data Modeler solved some minor issues I was hitting that I had not had time to research. Better add this to the presentations this summer!

Leave a Reply

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