Problem faced due to skipping sequence numbers when generated from inside JMS Queues during message

[Meena Ajay]

Hi All,

This is a query regarding generation of reference numbers using sequence from inside JMS Queues

Current implementation of the project is that an order processing system uses oracle sequence number for generating the sequence numbers which needs to maintain the order in which the reference numbers are generated.
Ordered keyword is used when the sequence was created and CACHE is not set.
This invocation to retrieve the sequences is from inside a JMS Queue.
If there are any business reasons for which a transaction is rolled back or retriggered (based on redelivery limit), the sequence numbers are generated but unused and finally when the transaction commits after multiple redelivery attempts, there are multiple sequences skipped.
Finally the order reference numbers do not have consecutive numbers and has business implications.

It would be of great help if someone could input for the following query

1)Is there any option provided in JMS/Oracle that when a prepared statement is executed to retrieve sequences from oracle sequence number, it retains the same number for every redelivery attempted?
2)Or is there any other option with which the sequences can be generated and set in the JMS Message object itself and be retrieve on the next redelivery attempt?

Thanks in advance,
Meena

[Paul Sturrock]

Sequences do not guarentee that the numbers returned are sequential, they only guarentee they will be ordered and unique. There would be a performance overhead trying to do this and, considering what sequences are for, its not a featrue that is required. So there is nothing in Oracle to let you tell it you didn't use the sequence value so don't increment.

2)Or is there any other option with which the sequences can be generated and set in the JMS Message object itself and be retrieve on the next redelivery attempt?

You certainly could maintain a counter in your applciation. However, it would be accessable to your application alone and would not work in a cluster.

Why is the order reference important? Sequences are usually only ever used for surrogate keys.