1

Explanation: There are 2 tasks A & B which are scheduled daily & monthly respectively.

Nature of Tasks:

  • All the tasks have a seq_number assigned to it which is unique.
  • This Number is assigned by using MAX(seq_number)+1 from the table where the tasks are stored.
  • Once the task has completed & the day has changed to schedule the next task, the older task will be deleted and a new 1 will be added.
  • Until the next schedule the old task records are retained.
  • seq_number is primary key for the table where these records are stored.

eg. Task A is scheduled for 30-Jun-2016 with seq_number 1, Task B is scheduled for Jun-2016 with seq_number 2. Once Task A for 30-Jun-2016 is finished its deleted and a new task will be added for 01-Jul-2016 with seq_number 3. Until the end of July the task A will keep deleting seq_number 3 record and added another task A for next day with seq_number 3. Once we reach the end of July, task B will be deleted and added with seq_number 4. Now when the task A is scheduled for 01-Aug-2016 it will take the seq_number 5.

As you can see the seq_number keeps on increasing and one day it will cap out.

My Solution:

Instead of taking max +1 we can randomly get any number in the range and assign. If we get Primary Key Unique Constraint Error then we can again randomly find another number in a loop until we find a free one.

Please advise feasible ways to fix this issue. All ideas are welcome.

edit: seq_number column is NUMBER(6) and it cannot be changed easily. The solution should be able to reuse the seq_numbers.

pOrinG
  • 896
  • 3
  • 13
  • 27
  • 1
    "*This Number is assigned by using MAX(seq_number)+1*" that is a **very** bad idea. Why don't you simply use a real SEQUENCE? If you are afraid you will be running out of numbers: if you burn 10000 sequence numbers **per second**, 24 hours a day, 365 days per year you will run out of numbers in 31709791983764586 years - I would be **very** surprised if your software will still be running then –  Jul 18 '17 at 05:33
  • @a_horse_with_no_name Initially the design was to delete the records once the task has completed instead of retaining them, hence the MAX(seq_number) logic. In this case the sequence was never exhausted. However we have recently decided to retain the latest task details. Thanks for the real sequence idea. – pOrinG Jul 18 '17 at 06:03
  • Even with the broken `max() + 1` design the sequence would never be "exhausted" - the time needed to run out of numbers only depends on the data type chosen, and in a `number(38)` column the maximum value is 9999999999999999999999999999 regardless on how you generate that number. Don't be afraid of gaps in sequences if the the only job of that number is to be unique. –  Jul 18 '17 at 06:06
  • 1
    Why can't you change the data type of the column? Using a `number(6)` for this is the actual problem. You should try to fix **that** instead of finding workarounds for a wrongly chosen data type –  Jul 18 '17 at 06:08
  • hi @a_horse_with_no_name , the issue i am facing is that the column data type is number(6) and its used in many places outside oracle db. Eg. c programs where its datatype & size is hard coded so it wont be easy to extend it to number(38). – pOrinG Jul 18 '17 at 06:08
  • @a_horse_with_no_name That's why i am looking for solutions which can reuse the older numbers. The seq_number is generated in PLSQL procedure and the logic can be changed easily. – pOrinG Jul 18 '17 at 06:11
  • 1
    If I read the question correctly this is essentially the same problem than [An “entity” specific sequence](https://stackoverflow.com/q/38913246/272735). You should be able to implement the solution suggested by @matthew-macpeak – user272735 Jul 18 '17 at 06:41
  • 1
    Can you just have it cycle back to 1 when it hits the limit, like a regular sequence? – William Robertson Jul 18 '17 at 06:41
  • @WilliamRobertson You are right, it would have been a good idea to cycle back to 1 when it hits the limit, however currently we are not using a oracle sequence but max(seq_number)+1 logic. Thanks for your input. – pOrinG Jul 18 '17 at 07:50
  • @user272735 The question you shared is very close to what I was talking about. I got the idea on how to go about my solution. Thanks!!! I am marking as a duplicate :) – pOrinG Jul 18 '17 at 07:51
  • If you can't implement any suggested changes then why are you asking the question? – William Robertson Jul 18 '17 at 07:52
  • @WilliamRobertson I can actually implement the solution suggested in the question shared by "user272735" above. It solves my problem. The sequence solution is not going to help me here in my current design. Thanks for your input. – pOrinG Jul 18 '17 at 07:54
  • I wasn't suggesting a sequence. I was suggesting adding an exception handler to your existing implementation that sets the value to 1 if it's unable to increment it. – William Robertson Jul 18 '17 at 08:10
  • @WilliamRobertson If using exception handler i reset the value to 1, the max(next_sequence) + 1 value will still be out of limit the next time i try. In this case when my exception handler resets it to 1 i will get another constraint error. Also please note that the example shows tasks A & B however in total there will be around 2000 unique tasks at a given point. – pOrinG Jul 18 '17 at 08:13
  • Ah, good point. – William Robertson Jul 18 '17 at 08:36

0 Answers0