Re: temporarily stop and then restart autonumbering of primary key field

brightspot wrote:

> My table has a primary key field with the values 1 through 5 in it. I
> want to suspend autonumbering, set the next value to 38, resume
> autonumbering and have Oracle continue on with value 39. The column
> would look like this:
> 1
> 2
> 3
> 4
> 5
> 38
> 39
> 40
> ...
>
> Is this possible using PL/SQL from a stored procedure?

Yes. Simply fast forward the sequence. Something like this:

loop

   select seqname.nextval into i from dual;    exit when i >= 37;
end loop;

Re-creating the sequence is not such a good idea as it will break (invalidate) existing code using that sequence, there are potential issues of grants and so on. All solvable problems - but I will rather simply fast forward like above as it will have no impact on existing running software, whereas dropping and re-create an object does.

--
Billy