1

In an Oracle PLSQL function I want to generate a special kind of sequence number. It starts with 00000001, but when 00899999 is reached, I want to have 01000001 as its next number. This pattern repeats when 01899999 is reached: The next number then should be 02000001.

To put it another way: My goal is to skip the numbers in the sequence in the range between ##900000 and ##999999, where ## denote two digits.

How can this be done on an Oracle database?

  • I think your design and "requirement" are fatally flawed. Oracle sequences are NEVER guaranteed to be "gapless". Suppose you "pull" a sequence, but then for whatever reason decide to roll back the transaction. That sequence number is lost forever. Or suppose the database is shut down or the pool is flushed due to memory constraints. All unused sequences in the cache are lost. And if you try to create your own sequence mechanism by looking at the max of some number in a table, you will necessarily have to serialize it's use, making it unsutible for a multi-user system. – EdStevens Sep 30 '17 at 19:43
  • @EdStevens Thanks for your thoughts on this! Please keep in mind that I have never asked for a sequence that has gaps ONLY in a certain range. My requirement is that I have a sequence which does not produce numbers in a certain range, but otherwise to act the same (or similar) as an Oracle sequence. Pulling numbers as max from a table or to use a table on its own for storing the current sequence's value needs to be guarded by a locking-mechanism to make it work in a multi-user environment, as I have pointed out in the comments below to RDFozz's answer. – mruether Oct 2 '17 at 11:05
  • @EdStevens RDFozz solved this by a neat SELECT FOR UPDATE. – mruether Oct 2 '17 at 11:06
2

If you're looking for an algorithm:

  • Add 1 to previous number;
  • if (current number + 100000) % 1000000 = 0, add 100001 to current number.

So:

  • 899998 + 1 = 899999; (100000 + 899999) % 1000000 = 999999; return 899999.
  • 899999 + 1 = 900000; (100000 + 900000) % 1000000 = 0; 900000 + 100001 = 1000001; return 1000001
  • 1000001 + 1 = 1000002; (100000 = 1000002) % 1000000 = 100002; return 1000002 ...
  • 1899998 + 1 = 1899999; (100000 + 1899999) % 1000000 = 999999; return 1899999
  • 1899999 + 1 = 1900000; (100000 + 1900000) % 1000000 = 0; 1900000 + 100001 = 2000001; return 2000001
  • and so on.

One implementation:

  • CREATE TABLE myNumSeq (lastNum int); INSERT INTO myNumSeq (0);
  • Create a function, fn_GetNextNum(), that works something like this:

    DECLARE @theNum int;
    BEGIN TRANSACTION;
    SELECT TOP 1 @theNum = lastNum + 1 FROM myNumSeq FOR UPDATE;
    IF (@theNum + 100000) % 1000000 = 0
    BEGIN
        SET @theNum = @theNum + 100001;
    END;
    UPDATE myNumSeq SET lastNum = @theNum;
    COMMIT TRANSACTION;
    RETURN @theNum;
    

Add error handling as appropriate. I haven't used Oracle regularly in about 18 years, so please consider the above pseudocode. I leave the Oracle-specific details (can't recall if it has both procedures and functions, or the return mechanism used for something like this if it only has procedures) to someone familiar with that.

I'm sure this could be written more concisely, but for single-record additions it should work fine. If you're adding records in bulk, using a variation on a number table would probably be quicker than a function.

  • Thanks for your answer. I have clarified the question. Unfortunately the pattern repeats when 01899999 is reached. Also I am looking for some implementation details in PLSQL. – mruether Sep 29 '17 at 9:39
  • There seems to be some logic issue in your explanation? first time you want to go from 899999 to 1000001, skipping 1000000 and next time from 1899999 you wish to go to 2000000. Right? You probably need to write a function with two sequences that generate what you need and return the value to the caller. One of those would start at 1 and when you reach 899999, re-starts at 1. Another one that starts at 0 and increments by 1000000 when first sequence resets. I'd caution that this function will likely limit your performance scaling. – Raj Sep 29 '17 at 12:29
  • @Raj: Thanks for pointing that out. Its actually starting with ...0001 every time. – mruether Sep 29 '17 at 13:46
  • The formula I specified took into account that this should work at 899999, 1899999, 2899999, etc. - that's why I'm using modulo. FYI, added pseudocode - haven't used Oracle in ages, so not sure of the specifics for making it a function. – RDFozz Sep 29 '17 at 15:15
  • @Raj - that seems like a perfectly valid answer of its own. Why don't you add it? – RDFozz Sep 29 '17 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.