# Customization generator SQL

**URL:** <https://forums.toadworld.com/t/customization-generator-sql/31617>\
**Category:** Toad Data Modeler\
**Created:** [November 7, 2014, 8:07am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617 "2014-11-07T08:07:14Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![DenIro](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@DenIro](https://forums.toadworld.com/u/DenIro)\
**Post date:** [November 7, 2014, 8:07am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617/1 "2014-11-07T08:07:14Z")

</div>

Hello,

I’m using Toad version 5.1 with a customized generator SQL File for Oracle 10g.

Moving on to the new version 5.3 I can not bring changes to the generator.

I should customize the generation of the sequence and the trigger of the sequence.

Example

/\* Sequence for table MAIN\_CONTEXTS linked to attribute MAIN\_CONTEXT\_ID \*/  
CREATE SEQUENCE “MAIN\_CONTEXTS\_SEQ”  
INCREMENT BY 1 START WITH 1 MAXVALUE 2147483647 MINVALUE 1 CYCLE NOCACHE ORDER

/

– Trigger for sequence MAIN\_CONTEXTS\_SEQ for column MAIN\_CONTEXT\_ID in table MAIN\_CONTEXTS ---------

CREATE OR REPLACE TRIGGER “T\_MAIN\_CONTEXTS\_SEQ” BEFORE INSERT  
ON “MAIN\_CONTEXTS” FOR EACH ROW  
BEGIN  
SELECT MAIN\_CONTEXTS\_SEQ.nextval INTO :new.“MAIN\_CONTEXT\_ID” FROM DUAL;  
END;  
/

Now

Sequence not generator because is set to not generate in the model.

– Trigger for sequence MAIN\_CONTEXTS\_SEQ for column MAIN\_CONTEXT\_ID in table MAIN\_CONTEXTS ---------

CREATE OR REPLACE TRIGGER “ts\_MAIN\_CONTEXTS\_MAIN\_CONTEX\_0” BEFORE INSERT  
ON “MAIN\_CONTEXTS” FOR EACH ROW  
BEGIN  
SELECT “MAIN\_CONTEXTS\_SEQ”.nextval INTO :new.“MAIN\_CONTEXT\_ID” FROM DUAL;  
END;  
/

thanks

---

<div class="post-metadata">

**Author:** ![Lukas.Knapek](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/lukas.knapek/32/269_2.png) [@Lukas.Knapek](https://forums.toadworld.com/u/Lukas.Knapek)\
**Post date:** [November 10, 2014, 2:25am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617/2 "2014-11-10T02:25:05Z")

</div>

Hello there,

Some functions in TDM 5.3 have been rewritten, which may be the reason your customization no longer works.

Could you send us the package which contains your customized function? We can take a look and see what we can do.

Regards,

Lukas

---

<div class="post-metadata">

**Author:** ![DenIro](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@DenIro](https://forums.toadworld.com/u/DenIro)\
**Post date:** [November 10, 2014, 3:15am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617/3 "2014-11-10T03:15:44Z")

</div>

Hi,

I had modify GenerationsOR.txg, that now is not possible.

I attached the function modified.

I ask how create the same results without change a system file.  
[CreateAttrTrigSequences.zip](https://global.discourse-cdn.com/quest/original/2X/3/32c398e9fe4e9960a4013f3728f964a5f582f52f.zip) (1.3 KB)

---

<div class="post-metadata">

**Author:** ![Lukas.Knapek](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/lukas.knapek/32/269_2.png) [@Lukas.Knapek](https://forums.toadworld.com/u/Lukas.Knapek)\
**Post date:** [November 10, 2014, 6:38am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617/4 "2014-11-10T06:38:54Z")

</div>

Oh you’ve sent us the script itself, not the package. Nevermind that, one of our devs has created a package for you. Just download it and put it in the user packages folder, which should be located in

**C:\Users\<USER\>\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}**

After you move the package there, open TDM and try the Oracle 10g generation now. The script from the package should be used. You can edit it in the future and it will still be used instead of default TDM 5.3 generation method.

Let us know how it worked out for you!  
[GenerationsOR\_user.zip](https://global.discourse-cdn.com/quest/original/2X/9/953cb90f95f6c8828fab291616e3d6164ad67181.zip) (2.15 KB)

---

<div class="post-metadata">

**Author:** ![DenIro](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@DenIro](https://forums.toadworld.com/u/DenIro)\
**Post date:** [November 10, 2014, 7:09am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617/5 "2014-11-10T07:09:16Z")

</div>

Fantastic, works great.

I looked at the package but what you have done is not documented anywhere ☹

How can I maintain the same order of generation indices foreign key.

Before the index was created just before the foreign key while now just after the creation of the table.

How find a FKIndex from FK in macro script?

Thank you so much

---

<div class="post-metadata">

**Author:** ![Lukas.Knapek](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/lukas.knapek/32/269_2.png) [@Lukas.Knapek](https://forums.toadworld.com/u/Lukas.Knapek)\
**Post date:** [November 11, 2014, 12:29am UTC](https://forums.toadworld.com/t/customization-generator-sql/31617/6 "2014-11-11T00:29:12Z")

</div>

First, let me elaborate on why it isn’t possible to customize some of the system functions anymore (for example the Oracle 10g generation). We are currently in the process of rewriting many of TDM functions in Delphi. Rewritten functions are not customizable any more in in the way you are used to, but they are significantly faster. The Oracle generation functions are rewritten already in 5.3, that’s why you weren’t able to find them in Package/Script Explorer.

To answer your questions:

_I looked at the package but what you have done is not documented anywhere_

It’s a process that isn’t fully documented because most TDM users won’t probably ever need this and there also exists the danger of doing something wrong and blocking some of the TDM functions, if not the whole application. But basically in the package we used Metamodel where we configured your customized function (**CreateAttrTrigSequences()**) to be used instead of the default TDM function.

There is some information on Metamodels in TDM Help ( **Projects and Models \> Scripting and Customization \> Metamodel** ) but it doesn’t really go deep. There is also an interesting article on the Wiki which is related to the topic and describes how TDM allows you to add your own functionality by using your own packages - that is the process we used in your case.

_How can I maintain the same order of generation indices foreign key. Before the index was created just before the foreign key while now just after the creation of the table._

This needs some explanation as well. In TDM 5.1, the foreign key indices were created just before the foreign keys themselves, but only during the generation of DDL script (the indices didn’t exist in the model). This caused some trouble in the long run so starting with TDM 5.2 the FK indexes are by default created after creating a table they belong to, just like other indexes (they are now part of the model).

In **Model Menu \> Order of Generated Objects \> Extension Tab** you can set if the indexes will be created after each table, or all at once after all tables are created. If you’d like to create them just before FKs, feel free to submit the idea to the Idea Pond and we will consider implementing it.

_How find a FKIndex from FK in macro script?_

You can test a relationship and see, if it there are any FK indices associated with it by doing the following:

**FKIndex = Relation.ForeignIndex** // If the relation has no FK index associated with it, _FKInxed will be NULL_

Or, if you want to know if an index is a FK index, try this:

**Relation = Index.RelationSource;**  
**if (Relation != null)**  
**Log.Information("Index is FKIndex for relationship: " + Relation.Name);**

Hopefully this wall of text will help you 🙂

Regards,

Lukas

\*\*