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:
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:
