How to modify oracle DDL generation to generate sequences and triggers for a specific domain attribute (IDENTITY) ?
Extend “Generations for Oracle” package ?
I was a Erwin user. It was easy to do it with a “Script Template”. How to do it with Toad data modeler ?
Hello Olivier,
We’re sorry, we are not definitely sure what you need. Do you want to change anything in the
generated DDL code? If so, please write us more details, an example how it is generated in TDM
and how you want TDM to generate it. Thanks in advance.
Note: Please see the Attribute Properties dialog, box “Used Sequence (trigger)”. From this box, you can select a sequence that you want to use for the attribute.
(A new trigger will be created in SQL script.)
Regards,
Vladka + TDM Team
Hello Oliver,
Oracle has no IDENTITY functionality. You have to use a sequence
( Entity | Attributes | select the field for the “Identity” | Used Sequence
Define one ).
Before you generate the script for creating table with column, select
Model | Generate DDL script | Detail settings | chekc box “create triggers for update of columns that use sequence”.
That generates a trigger which updates the sequence every time you use it.
In the sequence you can define start value, stepping an so on.
Best regards
Linus
I think I am looking for the same answer as Olivier K. If I have a Physical Schema based on Microsoft SQL Server and have an identity column defined, I would like to have this converted to a sequence when I convert the schema to an Oracle schema.
In Erwin, you would add code into the equivalent of After Script (I forgot it has been awhile since I used Erwin) that reads something like this:
if( %DBType% == “Oracle”) then
create sequence foo
end if
This allows for an easier transition to Oracle script from the Microsoft SQL Server model.
Hi,
this is doable via scripting, however, I think I will be able to write you more information on 4th January, that’s why I have to ask You for patience.
Have a nice day,
Vaclav
Hello all,
Please find attached a system package that converts Identity to Sequences and back during conversion from Oracle to MS SQL and back.
Copy the .txg file to the location where you store user packages. Default location is:
C:\Program Files\Quest Software\Toad Data Modeler 3\Packages\System
If you have any questions, please write us back.
IMPORTANT NOTE: Before you install next TDM version, you have to delete the packages.
OR
Delete them now and use the new packages in next post of this thread (of 12 January). The new packages don’t have to be deleted before the next installation of TDM.
Thanks.
Regards,
Vladka + TDM Team
Message was edited by: vladka- added note
ConvertMSOR.zip (2.25 KB)
Hi again,
Please find attached new system packages that convert Identity to Sequences and back during conversion from Oracle to MS SQL and back.
If you used the previous package of 7 January (see my previous post), please delete it and use this new one.
If you do not do it, you will have to delete it before you install next TDM version.
This new package doesn’t have to be deleted before the TDM installation.
The steps to use the new package are the same:
Copy the .txg file to the location where you store user packages. Default location is:
C:\Program Files\Quest Software\Toad Data Modeler 3\Packages\System
If you have any questions, please write us back. Thanks.
Regards,
Vladka + TDM Team
ConvertORMS.zip (2.44 KB)
So “identity” can only be indicated on a physical model, right? Too bad. It would be great if I could specify that on the logical model and have the conversation take care of the implementation.
Anyway, on the PER side, when I look at an attribute’s “Used Sequence (trigger)” my only available selection is “-- None --”. How do I get another selection?
Hi,
when I look at an attribute's "Used Sequence (trigger)" my only available selection is "-- None --". How do I get another selection?
No sequence exists in your model, therefore there isn't any selection. So, click the small icon next to the Used Sequence (trigger) box, create a sequence in the Sequences dialog (see the screenshot).
If you have more questions, please write me back.
P.S. TDM upcoming version 3.5.10 will bring the following improvement:
- Conversion from Oracle to MS SQL (and MS SQL to Oracle): Automatic conversion of Identity in MS SQL to Sequence in Oracle (and back).
- Conversion from Oracle to MySQL (and back): Sequence in Oracle are converted to Autoincrement in MySQL (and back).
- Conversion from MS SQL to MySQL (and back): During conversion of Identity (MS SQL) to Autoincrement (MySQL) and back the new IdentitySeed/Initial Autoincrement is taken into consideration. (In previous version, only conversion between checkbox Identity and Autoincrement was possible.)
CR 70 025
Regards,
Vladka
