Oracle Sequence in CDT

anilkumark over 7 years ago

There is a table which is using oracle sequence as a PK. I am using below annotations in XSD, the problem here is all the EMP_IDs creating in jumbling order like (1,2,3,4,13,14,5,6).

<xsd:element name="EMP_ID" type="xsd:integer">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@Id @Column(name="EMP_ID", columnDefinition="NUMBER NOT NULL") @SequenceGenerator(name="EMP_ID_SEQ_generator", sequenceName="EMP_ID_SEQ", initialValue=1) @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="EMP_ID_SEQ_generator")</xsd:appinfo>
</xsd:annotation>
</xsd:element>

Discussion posts and replies are publicly visible

0 chandu
A Score Level 2
over 7 years ago

could you please attach the sequence script?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chandu
A Score Level 2
over 7 years ago

Please check the "ORDER" attribute in the sequence that you have created.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 aloks0189
Certified Lead Developer
over 7 years ago

@anilkumark Did you come across through any scenario where, while inserting rows some error occurred, Because as you are using sequence, once that is got executed successfully or not but it's value used to get increase. Before inserting a record in DB first Hit the following query in Oracle DB
============================
sequence.CURRVAL
============================
And observe whats your current value of your Sequence on Oracle Console, then try inserting a record in DB using Appian, and observe did you get the Correct Primary Key (Next One). This generally cause when the Sequence gets invoke improper

If this do not work out, then try Creating trigger on top of Your Sequence

Hope this Will help You
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 felixl973 over 7 years ago

If your sequence is created with default settings its possible that your oracle sequence has the default cache of size 20. and if your oracle db application is run in a cluster, each app will have their own reserve of cache sequence IDs.

You could alter the sequence to have NOCACHE
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rawich over 7 years ago

Based on your sequence of numbers, it's very likely that you're using Oracle RAC (Real Application Clusters). If you modify the sequence to have "ORDER" then you can avoid the out of order numbers.
docs.oracle.com/.../statements_6017.htm

But if the order and continuity (no skipping) of the primary keys are absolutely important, you might want to consider using Appian constant together with Increment Constant smart service instead.
forum.appian.com/.../Increment_Constant_Smart_Service.html
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 anilkumark over 7 years ago

Below is the sequence script we are using.

CREATE SEQUENCE EMP_OWNER.EMP_ID_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel