Toad World® Forums

Generating the DDL Script

Hi, I am a new user of Toad Data Modeler version 5.4.8.1

I work in a Oracle 12 environment.

The Data Modeler is simple and very intuitive to use. Nevertheless I had some requests for the interface (I will post a new message) and mainly for the scripting generation.

Problems when generating DDL Script:

  1. There is no DROP TABLE clause, although I checked the “Drop tables with Cascade constraint clause” switch (see Menu - Model - Generate DDL Script - Edit default settings - Datail settings tab)

  2. There is a sintax error when creating attributes. Toad creates a script using double quotes that are not required

CREATE TABLE ALL11_LOOKUP (“LOOKUP_TYPE” Varchar2(30 ) NOT NULL …

while the correct sintax is without quotes

CREATE TABLE ALL11_LOOKUP (LOOKUP_TYPE Varchar2(30 ) NOT NULL …

  1. There is a sintax error in CHECK Clause. The checked field is not specified

ENABLED_FLAG Varchar2(1 ) NOT NULL CONSTRAINT Flag_Enabled CHECK ( in(‘Y’, ‘N’)),

while the correct sintax is obtained repeting the field

ENABLED_FLAG Varchar2(1 ) NOT NULL CONSTRAINT Flag_Enabled CHECK ( Flag_Enabled in(‘Y’, ‘N’)),

  1. The costraint name acceptes the “minus” sign, while Oracle doesn’t accept that notation

ALTER TABLE ALL11_Chiavi ADD CONSTRAINT Lookup-Chiavi FOREIGN KEY …

i corrected the constraint names using underscores

ALTER TABLE ALL11_Chiavi ADD CONSTRAINT Lookup_Chiavi FOREIGN KEY …

It’s very important to me to understand if I can use Toad Data Modeler as a complete and fully functional software. Otherwise I have to look for other products (I tried without success Enterprise Architect)

Every comment/suggestion will be greatly appreciated.

Hello there,

Two of your questions can be answered right away:

1 - Checking the option means that “CASCADE CONSTRAINTS” clause will be added to all drop statements in the generated script, it doesn’t enable/disable drop statements themselves. You can choose SQL statement used for each object type in the grid on What to Generate tab. Simply change the value of the Extended Value column.

4 - As a general rule, TDM does not automatically correct user entered values to match the syntax rules of a given database platform. There is Verification system, which analyzes your model and then displays various errors, warnings and hints, depending on the verification rules enabled/disabled. Sometimes you can repair the rule violation right away using Quick Fix. We could possibly create a new verification rule which addresses the constraint syntax, if you feel it would be useful.

As for questions 2 and 3, we will have to look into this, so please give us some time.

Regards,

Lukas

Lukas, first of all, thank you for your answer.

I solved point #3 (error in Check) because the check clause that I wrote was incorrect. However, as for the point #4, even using Verification, no warnig arrived from the system. I think it would be useful to check the sintax before generating script.

About #1 (drop table), please help. How can I force the DROP TABLE scripting?

Hello again,

As for #1, look at the screenshot:

About #2 - TDM either quotes all object names, or none of them. There should not be a case where one object is quoted while other object is not. The part of the CREATE TABLE statement you posted should be syntactically valid, the quoted attribute type should not cause the problem. Are you sure the rest of the statement is correct?

You have figured out #3 on your own, so just some more information about #4: Using the "minus" character in object name is valid as long as the object name is enclosed in quotation marks. But as said before, TDM does not automatically change user entered values.

I have created a new CR: TDM-1471. It's about implementing a new verification rule, which checks object names when "Use Quotation Marks" option is disabled to see whether they will cause syntax errors. This way, you should get a warning in situations such as #4.

Regards,

Lukas