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.
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:
- No storage parameters
- No schema notation
- 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.
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”.
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.
34 Comments
Dear Jeff,
your post shows, that the DDL generation will generate the trigger code. Up to version 18.1 that works fine for me. But in 18.4 the preview does NOT show any triggers. If I open the same model file with 18.1 the DDL preview shows still the triggers, i.e. they are “there”.
I assume that there is some missing “check mark” to generate the triggers in data modeller 18.4 as well. But: where is that check mark?
Thanks in advance
Frank
Hi,
I am using the Export utility to generate the ddl script for a number of tables, and getting errors in the Constraints section when I execute the script. The primary keys column uses and identity sequence. In the generated script I also get an alter statement that I think shouldn’t be there (alter … column … not null). If I uncheck Constraints then I will not get the alter table … primary key statement. Is there a way around this issue?
CREATE TABLE “MY_TABLE”
(“COLUMN_1” VARCHAR2(255 CHAR),
“MY_TABLE_SID” NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 11 NOCACHE ORDER NOCYCLE NOKEEP NOSCALE
) NO INMEMORY ;
——————————————————–
— Constraints for Table MY_TABLE
——————————————————–
ALTER TABLE “MY_TABLE” MODIFY (“MY_TABLE_SID” NOT NULL ENABLE);
ALTER TABLE “MY_TABLE” ADD CONSTRAINT “MY_TABLE_SPK” PRIMARY KEY (“MY_TABLE_SID”)
USING INDEX ENABLE;
Error starting at line : 996 in command –
ALTER TABLE “MY_TABLE” MODIFY (“MY_TABLE_SID” NOT NULL ENABLE)
Error report –
ORA-01442: column to be modified to NOT NULL is already NOT NULL
01442. 00000 – “column to be modified to NOT NULL is already NOT NULL”
*Cause:
*Action:
Table “MY_TABLE” altered. —-> this is for the pk constraint
I’m on vacation.
Please post a scenario on the forums…so get the original DDL for a table and share so we can reproduce your observed behavior.
Thanks. Done: https://community.oracle.com/thread/4161819
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
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.
When exporting DDL – How to disable the quotation mark around the column names and around the table name?
It’s generated by the database, and there’s no option to disable it. It’s been on my wish list for a long time though. So you’re not alone here.
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.
>>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.
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
on the SQL page in the table editor in SQL Developer or the generated SQL that the modeler generates based on your design?
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.?
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.
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.
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.
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.
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.
(This is SQL Developer version 4.0.3.16 and I’m operating in a 10g environment.)
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
Exactly. Huh. I wonder what is going on? I must have flipped a switch somewhere…
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
you can def drill-down into objects, ctrl-mouse hover over the text, it will hyperlink, then click to open. I’ve got a post here somewhere that describes it in more detail
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
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.
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
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.
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
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
That’s pretty easy actually. You need an after update trigger that fires a call to send mail via UTL_SMTP.
I don’t write code by request though…
Forgot to mark for the notification. Sorry for this second post.
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?
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.
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!