CODEWITHSUNDEEP
javaoracle-database
HarshaKA
HarshaKA

Reputation: 165

Java/SQL Creating custom sequence/creating a sequence based on two sequences

I have a requirement of creating a unique cyclic sequence which is the concatenation of two individual sequences.

Sequence 1 range is from- 1001 to 1040 Sequence 2 range is from- 220 to 240

The logic to generate the sequence is first increment sequence 2, keeping sequence 1 as-is. Once sequence 2 reaches 240, need to increment sequence 1.

The sequence will be something like below:-

1001-220 1001-221 1001-222 .... .... 1001-240 1002-220 1002-221 1002-222 .... .... .... .... 1040-240 1001-220

This is pretty simple if there is just one process/JVM that needs this sequencing, in which case I can just have two static variables and implement this logic.

But the sequences can be used by multiple processes sitting on different JVMs and hence needs to be thread-safe.

The next thought I had was to use Oracle DB sequences for both. But this can miss out some sequence numbers due to multiple parallel calls coming from different systems.

Is there a better way to do this?

Upvotes: 0

Views: 308

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Firstly, your two sequences are trivially mappable to a simple sequence of integers, i.e.:

 1 -> 1001-220
 2 -> 1001-221
 etc.

To calculate the two values given any integer value, use the following formulae:

n -> TRUNC(1001+n/22) || '-' || (MOD(n-1,21)+220)

e.g.

26 -> TRUNC(1001+26/22) || '-' || (MOD(26-1,21)+220) -> 1002-224

Secondly, sequences in Oracle can never be guaranteed to be gapless; only unique. To make a gapless sequence generator you must introduce serialization, e.g. a lock, to ensure that only one session can get the next value at any one time.

Upvotes: 2

Related Questions