Create sequence with sql result in it

Question

I have a sequence like this

begin
 if :new."ID" is null then
     select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  end if;

is there a way to set the ID to be the next upcoming ID in the table?

For example:

if my current last row ID is 5

I want the new.id to be 6, so when the INSERT executes then it would have ID of 6

This is not possible with a sequence. A sequence gets you the next number; if you rollback, that number is lost and you'll have gaps. That doesn't matter with technical IDs, but you want your IDs gapless you say, so this method doesn't suffice. — Thorsten Kettner
– Thorsten Kettner, Commented May 15, 2017 at 18:17
Usually you'd use a sequence with a trigger (or an identity column instead when available - which uses a sequence behind the scenes) and a column that is big enough to guarantee that you won't have to recycle numbers someday. Thus you get increasing numbers. There can be gaps, but who cares; you can always use ROW_NUMBER to get contiguous numbers in the order of your ID column when you need them. — Thorsten Kettner
– Thorsten Kettner, Commented May 15, 2017 at 18:40

APC · Accepted Answer · 2017-05-15 18:13:28Z

1

IDENTITY column is now available on Oracle 12c:

create table t1 (
    id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    info VARCHAR2(10)
    );

APC

147k20 gold badges180 silver badges289 bronze badges

answered May 15, 2017 at 16:51

developer_hatch

16.3k3 gold badges47 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1