pl/sql : gaps in ordinal number

I have a tabel with a varchar2 , nr_id, which represents an ordinal number that should be unique.

At runtime users can insert new rows to this table, and I have to propose them a new ordinal number, which they can change. All values must be numeric ( the varchar2-type is just one of the mysterious things I came across in this application... :-) )

Right now the number I'm proposing is simply a select max +1. The problem is that when a user changes the proposed value into something very large all following records with continue with these large numbers and nr_id will be running out of space...

How to fill the gaps when proposing a new ordinal number ?

I could write some loop like this :
while dummy > 0 loop

    select count(*) into dummy from table where nr_id = to_char(i)     i:=i+1
end loop
BUT there are already 700000 records in table ... Any quicker solution ? Received on Mon Jul 12 1999 - 03:59:23 CDT