stand alone oracle sequence generators

[dmaxwell]

Hi All,

I'm wondering if there is a way to model oracle sequence generators that will generate the ddl required to create the sequence generators, without creating the pre insert trigger to auto-populate the column.

For my application, one service is going to create the record in the DB and then pass the PK to another app for further processing. Therefore, I want my app to know the value without having to re-query the database, so I want to select the sequence value myself.

Any suggestions are appreciated.

Thanks

Dana

[thomaskilian]

Dana, for such purpose I suggest to use an own code generation. You can create DDL using the standard generation as suggested here in a recent thread or you use EA_EXEC_ADDIN to make use of the automation. The latter can produce almost everything, but it needs a bit more effort to learn the framework.

[jaimeglz]

Hi, dmaxwell,

Here is what has worked for me:

1. Create a new "sequence" or "db_sequence" stereotype for classes. This is the stereotype you will use for your sequences, which you will model as classes.

2. Create one or several ordinary class diagrams with your tables joining their respective sequences with dependency links (stereotype <<call>>). This way you can keep track of which tables have collaborating sequences.

3. In other collaborations in this forum, I have described schematically how to use the custom code generation feature in EA, and how to create Oracle custom code templates (you can find a better description in EA help, anyway). I will include here only the code for the "sequence" stereotype override template, at the Class Body level:

CREATE SEQUENCE %className%
MINVALUE 0
START WITH 0
INCREMENT BY 1
NOCYCLE
NOCACHE
;

SELECT %className%.NEXTVAL
FROM dual;


4. You have to generate your sequences with Ctrl+G (and not as DDLs).

To my knowledge, sequences give the highest transactional performance (and practically no contention at all) when generating consecutives for your PKs. So they have become a very important part of my modeling, as well as of my database objects.

I hope this helps,

Jaime

[«Midnight»]

Folks,

I think I saw something on this recently. If I'm correct the answer was that EA does not do this - or at least not gracefully - at this time. I also think it was on the feature list for the upcoming version 6.5.

HTH, David

[dmaxwell]

Thanks Jamie.