Generating Oracle sequences in DDL?

[sfwalter]

Hi,

I am using 7.0.817 and I'm having problems generating Oracle sequences in my DDL, foreign keys get generated just fine.   On my table I make sure the database is 'Oracle'.   I have validated that I have "auto number" checked on the database column properties.

When I generate the DDL I make sure that "Generate Sequences" is checked.  However the sequences are not created in the DDL.  Any ideas?

tia, Scott.

[SGREEN]

Hi Scott,

I too am having the exact same problem.  Can't seem to get it to work nor can I find any help to solve my problem.  If you manage to figure it out, please post the solution.  I have just upgraded to build 818 hoping that would fix the problem, however, no such luck.

Thanks,

Sandra

[«Midnight»]

Sandra and Scott,

I'm guessing here, but perhaps I can move you in the correct direction.

A month or two ago someone was talking about this. The poster explicitly mentioned setting the start and increment values in the column properties dialog.

Perhaps you need to fully populate the dialog with non-default properties.

And just in case, have you confirmed that the Database for your tables is set to Oracle. Yes, I know you have, but I keep forgetting to reset the default DBMS in EA and run into this from time to time.

David

EDIT: I've just tried this out. You need to set all the 'Generate <whatever>" boxes on the Generate Package DDL. You'll suddenly get a (mostly useless) pile of SQL, including the DROP (if you selected the appropriate option) and CREATE statements for your sequence. Once you get to that point you can experiment with clearing options until you are confident about the ones you need.

HTH, David

[SGREEN]

Thanks for the post.  Turns out that you must include the GENERATE TRIGGER option, (all other generate options, other than GENERATE TRIGGER and GENERATE SEQUENCE may be turned off) which of course makes perfect sense now that I think about it, because it generates a PRE-INSERT trigger to select the next sequence value to populate the column.

Thanks

Sandra

[«Midnight»]

Yes Sandra, that does make sense.

I sort of remembered this from an Oracle project quite some time ago, but didn't quite remember which combination was required. So, after the default settings plus Generate Sequences gave me the same results as you got, I just checked everything and tried again. Once I got something (anything) useful I edited my post. I figured you needed to get productive, and could resolve the details while you worked.

David

BTW: Thanks for telling us what the correct options are. That will help the next person who stumbles on this.

[EDIT: I've sent a request to Sparx to update the documentation to: clearly state the need to check both options, and put something about Oracle sequences in the index.]

[SGREEN]

Last but not least, I was having additional difficulties because my settings for auto number generation kept disappearing.  Turns out that if you make any changes to the column settings, it erase the auto number generation settings.  So you must ensure that you set auto-number LAST.

[«Midnight»]

Last but not least, I was having additional difficulties because my settings for auto number generation kept disappearing.  Turns out that if you make any changes to the column settings, it erase the auto number generation settings.  So you must ensure that you set auto-number LAST.

Sandra,

Please submit a bug report on this. Search my recent posts (past few days) for a best practice on doing this.

David

[sfwalter]

I figured out how to get them generated!  

You need to make sure you have the triggers options enabled when generating the ddl.   EA creates triggers for the column that has the sequence for it.  

Seems like they should create the sequence without the trigger, but at least I got it to work.

[RoyC]

I have added notes to the two Generate DDL topics, and indexed the notes as Oracle   Sequence Options, DDL For Tables and DDL For Packages. These two topics are displayed when you press the Help button on the respective Generate DDL dialogs. The changes should wash through in the Release 7.1 Help.

Best Regards

Roy

[Luis J. Lobo]

The DDL generation of sequences in Oracle works fine to me, but the values NOMAXVALUE, MINVALUE, NOCYCLE, NOCACHE and NOORDER aren't customizable. I can't set the name of the sequence neither.

Is there any way to indicate these values? I'd tried with Tagged Values (of the Column with the sequence) but doesn't work.

Thanks,

Luis.