Data Definition Language (DDL) is used to describe an object in SQL. When you model a table in your relational model using Oracle SQL Developer Data Modeler, you are probably very curious as to the code that is being generated to represent that object.
You can peek into this code using your mouse –
You can right-mouse-click on an object and choose ‘DDL Preview,’ or you can use the keyboard shortcut, Alt+Shift+I.
Here’s trick #1:
Mouse Over an object to get the DDL
Once the previewer is open, it’s dynamic. So click on another object in the model, and it will auto-refresh.
And trick #2:
Change How the DDL is generated on the fly
Open the Preferences dialog under the Tools menu and proceed to the DDL page. There you can specify how to generate the DDL including:
- Use Quoted Identifiers — this drives me nuts!
- Generate Inline Column Constraints
- Generate Comments in RDBMS
After you make this change, simply click back into the object and the DDL will auto-refresh based on your new setting. Here’s an example of how that looks with Quoted Identifiers.
The Options
Previewing the Code
You can expect more options and flexibility when it comes to generating the DDL in upcoming versions of SQL Developer Data Modeler. If you want to see something in particular be sure to add the request formally, or leave me a comment here.
10 Comments
hi,
may i have an help about ddl appearence, when i select DDL preview from a table on Phisycal Design i see this for trigger syntax, there are all LF in bad position
CREATE OR REPLACE TRIGGER USER.TR00_MY_TAB
BEFORE INSERT OR UPDATE ON USER.MY_TAB
FOR EACH ROW
ENABLE DECLARE
V_SYSTIMESTAMP TIMESTAMP(6) DEFAULT SYSTIMESTAMP;
BEGIN
—
IF NOT ( INSERTING AND USER = ‘XXXXX’ AND UPPER ( SYS_CONTEXT ( ‘USERENV’ , ‘OS_USER’ ) ) = ‘ORACLE’ AND UPPER ( SYS_CONTEXT (
‘USERENV’ , ‘HOST’ ) ) IN ( ‘XXXXX’ , ‘XXXXX’ ) AND UPPER ( SYS_CONTEXT ( ‘USERENV’ , ‘DB_NAME’ ) ) IN ( ‘XXXXX’ ,
‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ , ‘XXXXX’ ) ) THEN IF INSERTING THEN : NEW . ID_TMST_INS : = V_SYSTIMESTAMP ;
END IF ; : NEW . ID_TMST_UPD : = V_SYSTIMESTAMP ; : NEW . ID_DEN_FUNZIONE : = TRIM ( : NEW . ID_DEN_FUNZIONE ) ; : NEW . ID_DEN_OPERATORE : =
TRIM ( : NEW . ID_DEN_OPERATORE ) ; : NEW . ID_TXT_COMMENTO : = TRIM ( : NEW . ID_TXT_COMMENTO ) ; END IF ; EXCEPTION WHEN OTHERS
THEN RAISE_APPLICATION_ERROR ( – 20599 , ‘Errore Trigger: ‘ | | $$PLSQL_UNIT | | ‘ alla linea: ‘ | | $$PLSQL_LINE | | CHR ( 10 ) | |
TRIM ( SQLERRM ) ) ; END ;
/
————————————
i prefer this
CREATE OR REPLACE TRIGGER USER.TR00_MY_TAB BEFORE
INSERT OR UPDATE ON USER.MY_TAB
FOR EACH ROW
ENABLE DECLARE
V_SYSTIMESTAMP TIMESTAMP(6) DEFAULT SYSTIMESTAMP;
BEGIN
—
IF NOT (
INSERTING
AND USER = ‘XXXXXX’
AND UPPER(SYS_CONTEXT(‘USERENV’, ‘OS_USER’)) = ‘ORACLE’
AND UPPER(SYS_CONTEXT(‘USERENV’, ‘HOST’)) IN ( ‘XXXXXX’, ‘XXXXXX’ )
AND UPPER(SYS_CONTEXT(‘USERENV’, ‘DB_NAME’)) IN ( ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’,
‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’, ‘XXXXXX’ )
) THEN
IF INSERTING THEN
:NEW.ID_TMST_INS := V_SYSTIMESTAMP;
END IF;
:NEW.ID_TMST_UPD := V_SYSTIMESTAMP;
:NEW.ID_DEN_FUNZIONE := TRIM(:NEW.ID_DEN_FUNZIONE);
:NEW.ID_DEN_OPERATORE := TRIM(:NEW.ID_DEN_OPERATORE);
:NEW.ID_TXT_COMMENTO := TRIM(:NEW.ID_TXT_COMMENTO);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20599, ‘Errore Trigger: ‘
|| $$PLSQL_UNIT
|| ‘ alla linea: ‘
|| $$PLSQL_LINE
|| CHR(10)
|| TRIM(SQLERRM));
END;
/
thanks
Hi Jeff i find that if i use in the body of a trigger the syntax clause DECLARE thats give the problem about indentation that i observe when i produce the DDL Preview for a Table from a Phisycal model Design.
Using in a trigger body the syntax structure
DECLARE
BEGIN
END;
thanks Jeff, it’s possible in Oracle Data Modeler have a model to customize the layout of a ddl script before it was generate?
There are a ton of options for determining how your DDL will look, what information it will contain when you generate it from your design. What exactly are you trying customize? Not sure ‘layout’ makes sense for a text file…maybe give me an example?
this is an example of ddl script, i know that i can modify the visualization of the table syntax clause (PCTFREE etc properties) with a check in the menu Tool – Preference – Data Modeler – DDL and DDL/Storage panel.
Or decide something else (from 20.4.0.379.2205 version of SQL Developer) in Tool – Preference – Code Editor – Format like UPPER case o LOWER case for table name.
But if i want modify the PK clause and write the columns in same line (column1, column2, column3) or a line break before word PRIMARY KEY?
or having not shown the properties NOT DEFERRABLE in FK constraint clause?
or only one line break after a clause, sometimes i found most of one line break?
CREATE TABLE schema.table_name (
column1 NUMBER(9) NOT NULL,
…
column8 NUMBER(5),
column9 NUMBER(5)
)
PCTFREE 0 PCTUSED 0 TABLESPACE tablespace1 LOGGING
STORAGE ( PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );
ALTER TABLE schema.table_name
ADD CONSTRAINT xps_table_name PRIMARY KEY ( column1,
column2,
column3 );
ALTER TABLE schema.table_name
ADD CONSTRAINT fk01_table_name FOREIGN KEY ( column3 )
REFERENCES schema.table_other ( column3 )
NOT DEFERRABLE;
For the looks, yeah the formatter options is the way to go, look. For the actual content, there’s probably a way to define/hide those things as they’re generated. For this range of questions, I would suggest asking on the Data Modeler forums.
Hi Jeff,
Thanks for your website and all answers !
I have an issue with SQL Developer Data Modeler. Since I have versionned (with Subversion) my diagram I can’t export it to DDL statement for Oracle Databases.
I have post an question to Oracle Community (https://community.oracle.com/message/12629531) you will see a screenshot.
Thank you very much for help.
Jeff thanks for this informative website. Do you know of a way to limit the line length when generating table data insert statements? We are setting up a code and data deployment system and one problem is when “insert statements” are generated from existing table, sqldev sometimes generate lines with >2499 char length, and of course sqlplus errors in running those (SP2-0027). Thanks in advance for your tips!!
Very cool! I did not know about the mouse over trick to see DDL on any object in the diagram. (Plus the animation on the figure in the post is really cool.
I didn’t know about it either until the developer showed us in a team meeting. I figured it was hard to explain in words or even screenshots, so I built the animated GIF using Camtasia.