Using an Oracle DB, how retrieve the next value in a column of values in a table, starting from a random starting point in the column

Question

I have a tough problem I am trying to solve.

I have an Oracle Database table with a number of columns in it.

One of those columns is a Varchar2 column that has what amounts to a 5 digit counter in it (12345).

At the moment, there is a screen that a user enters data into, that relies on retrieving data using those values in the column. Additionally, it uses the knowledge of those values to determine what the next free value is.... in other words, if you were writing a new record, and 11111 was already used, then it would take 11112, and so on.

Now, it is entirely possible for there to be gaps in the numbers in the column... hence I need to know what the next FREE number is given a random start point in the column, and with a ceiling declared as the max available value as well.

Using only an Oracle DB solution, what would be the best way to tackle this?

I have been working on a server side solution... but alas, I feel this is really a job for the database server... not to mention, server side it's really slow. I feel that it really needs to be on the database server.

I would imagine a similar solution to what was devised server side would be used to work this on the DB server; ie starting from record x, increment each record from there by 1 until you either a) find NO record (which is what you want), or b) meet or exceed the ceiling.

I am not an Oracle guy; my background is SQL Server, but my current assignment requires use of Oracle.

I was looking at cursors, functions, packages... but I felt a bit overwhelmed. I felt that using a bulk data cursor was a good place to start, but really didn't know where to go from there.

Am looking for any advice on the best way to approach this scenario.

Thank you for taking the time to read this.

(EDIT) As I revealed in the comments below, the column in question actually contains two different types of numbering systems, base 10 (12345), and base 36 (1234A).

So, a better description of the problem would be,

Given a random starting point, and the max ceiling, solve for

the next FREE number

OR

the last used entry before the next gap begins.

I believe we have one potential answer so far that would work with either case.

Again, thanks everyone for your time.

Answer 1

The easiest way to find the first gap. Hmmm, if you take the difference between a sequence and a consecutive group of numbers, then the values are constant, until there is a gap. So, the following will identify each sequence of numbers separated by gaps:

select cast(counter as int) - row_number() over (order by counter)
from table t
where counter >= v_counter;

The first value is equal to v_counter - 1 for the first group. You want one more than this value. So:

select (case when max(counter) is null then v_counter
             else cast(max(counter) as int) + 1
        end)
from (select counter
      from (select t.*, cast(counter as int) - row_number() over (order by counter) as grp
            from table t
            where counter >= v_counter
           ) t
      where grp = cast(v_counter as int) - 1
      order by counter desc
     ) t
where rownum = 1;

The only complication that I can readily think of is when the initial value is already free. That is what the outer case takes care of.

Answer 2

You can use a CTE to generate the list of all possible values from your starting position from the maximum allows value, and then find the minimum value that doesn't already exist in your real data.

Sticking with varchar2 and using SQL*Plus bind variables as I don't know where yours are coming from:

var start_counter varchar2(5)
var max_counter varchar2(5)

exec :start_counter := '11111';
exec :max_counter := '99999';

with t as (
  select to_char(level + to_number(:start_counter)) as counter
  from dual
  connect by level + to_number(:start_counter) <= to_number(:max_counter)
)
select min(counter)
from t
where not exists (select 1 from t42 where t42.counter = t.counter);

It would be a bit neater with numbers, of course. And it somewhat assumes you have an index on the column in question; and if it does then this ought to perform a bit better:

with t as (
  select to_char(level + to_number(:start_counter)) as counter
  from dual
  connect by level + to_number(:start_counter) <= to_number(:max_counter)
)
select counter
from t
where not exists (select 1 from t42 where t42.counter = t.counter)
and rownum = 1;

But however you do this, you have a potential race condition if you have two sessions both looking for the next free value.

Answer 3

To find gaps you may use the LEAD function along with a substraction operation to determine where and how big are the gaps. For example:

SELECT seq_num, prod_id, next_seq_num, next_seq_num - seq_num - 1 gap
  FROM (SELECT seq_num, prod_id, lead(seq_num, 1, 0) over(ORDER BY seq_num) next_seq_num
           FROM tbl_seq_ex)
 WHERE next_seq_num - seq_num - 1 > 0

to test that query you may create the table with this instruction

CREATE TABLE tbl_seq_ex AS 
SELECT LEVEL seq_num, chr(ascii('A') - 1 + LEVEL) prod_id
  FROM dual
 WHERE NOT (LEVEL BETWEEN 27 AND 32 OR LEVEL BETWEEN 59 AND 64 OR LEVEL BETWEEN 91 AND 93 OR
        LEVEL IN (15, 46, 47, 85))
CONNECT BY LEVEL <= 100

Result

   SEQ_NUM PROD_ID NEXT_SEQ_NUM        GAP
---------- ------- ------------ ----------
        14 N                 16          1
        26 Z                 33          6
        45 m                 48          2
        58 z                 65          6
        84 ö                 86          1
        90 Ü                 94          3
6 rows selected