Friday, 20 March 2009

ADF BC: Using Groovy to fetch sequence numbers Part II

I recently posted ADF BC: Using Groovy to fetch sequence numbers for EO/VO attribute default values. This post showed the power of what you can get away with in the new Groovy expressions of ADF Business Components in JDeveloper 11g.

Though power is good (muhahaha!, um, cough), simple is better.

Steve Muench suggested the following to make retrieving sequence numbers a breeze via the Groovy expression facilities.

Readers will be familiar with the common recommendation for ADF Business Components to create a layer of framework extensions, as per Section 33.2 of the JDeveloper 11g Fusion Guide.

With this in mind we can create the following helper method seqNextVal() to fetch the next value for a named sequence in our custom EntityImpl:

...which we can then make use of in our Groovy expression in the Entity Object attribute's default value field:

Remember to set the Value Type = Expression.

Thanks to Steve for his suggestion.


Andrejus Baranovskis said...


We faced interesting issue while getting sequence value from ADF - database connection leaking. Connections are not released properly to connection pool, when accessing Sequence from Java or from Groovy.

Only way to avoid connection leaking - to close transaction, after getting new sequence number.

Will post blog on this.


Chris Muir said...

Thanks for letting us know Andrejus. When you blog about it could you include a bug # so readers can track the issue please, as well as a code description of the workaround.



shankar said...

I am faces similar problem, I have a an entity PRODUCT and PK is ID with a groovy expression attached to it as (new oracle.jbo.server.SequenceImpl("SEQ_PRODUCT",adf.object.getDBTransaction())).getSequenceNumber(). When I am creating new product with a popup it is incrementing, but in the popup if I cancel the action, still it will increment, as this should not happen. If you have any information on this scenario please post me.

Chris Muir said...

Are you saying the sequence is incremented twice? ... or ... on cancel you want to wind back the sequence number.

If it's the later, you've misunderstood database sequences. Once fetched/incremented they cannot be rolled back. From a development point of view you therefore cannot rely on them being sequential.