How To Implement Gapless Sequence in ADF BC

We all know how to insert new records using ADF BC. But if you have a sequence in the DB for primary key attribute and there is requirement to make sure assigned sequence values are always gapless - few extra steps are needed. When you hit Create button, ADF will assign sequence value immediately - often user may rollback transaction and sequence value will be lost. In order to prevent gaps in the sequence we can use two sequences - one temporary for Create and then real sequence will be applied during commit time from doDML method.

Download sample application - CreateSequenceApp.zip. You can access and run this sample deployed on Oracle Cloud instance - CreateSequenceApp on Oracle Cloud live.

There are two ADF BC framework methods overridden on EO level (you can do the same in generic class) - create() and doDML(). Firstly from create() method we are getting temporary value from the dummy sequence and assigning it:

Then finally when user decides to commit transaction and insert new row, in doDML() method we are substituting temporary key value with real value from the gapless sequence:

Sequence names are retrieved from attribute custom property:

Both sequences are defined in Oracle Cloud DB:

You can test it directly on Oracle Cloud live application instance - press Create button to insert new record. Temporary value 7 is assigned (temporary value can be hidden for new records, I made it visible only for the demo):

Type all mandatory values and commit:

Gapless sequence value 208 will be assigned:

Do a few more tests - insert new record but don't commit and press Undo:

Do the same thing twice, you should see that temporary sequence value is increasing with each time:

Insert new record again and complete transaction, you will see that next gapless value 209 is assigned: