How to create an Oracle sequence starting with max value from a table

Question

Trying to create a sequence in Oracle that starts with the max value from a specific table  Why does this not work   CREATE SEQUENCE transaction sequence   MINVALUE 0   START WITH  SELECT MAX trans seq no       FROM TRANSACTION LOG     INCREMENT BY 1   CACHE 20

User · Answer

Here I have my example which works just fine   declare  ex number  begin   select MAX MAX FK ID     1 into ex from TABLE    If ex  gt  0 then     begin             execute immediate  DROP SEQUENCE SQ NAME         exception when others then         null      end      execute immediate  CREATE SEQUENCE SQ NAME INCREMENT BY 1 START WITH      ex      NOCYCLE CACHE 20 NOORDER     end if  end

User · Answer

Based on Ivan Laharnar with less code and simplier   declare     lastSeq number  begin     SELECT MAX ID    1 INTO lastSeq FROM  lt TABLE NAME gt       if lastSeq IS NULL then lastSeq    1  end if      execute immediate  CREATE SEQUENCE  lt SEQUENCE NAME gt  INCREMENT BY 1 START WITH      lastSeq      MAXVALUE 999999999 MINVALUE 1 NOCACHE   end

User · Answer

you might want to start with max trans seq no    1  watch  SQL gt  create table my numbers my number number not null primary key    Table created   SQL gt  insert into my numbers select rownum from user objects    260 rows created   SQL gt  select max my number  from my numbers   MAX MY NUMBER  --------------            260  SQL gt  create sequence my number sn start with 260   Sequence created   SQL gt  insert into my numbers my number  values  my number sn NEXTVAL   insert into my numbers my number  values  my number sn NEXTVAL    ERROR at line 1  ORA-00001  unique constraint  NEIL SYS C00102439  violated  When you create a sequence with a number  you have to remember that the first time you select against the sequence  Oracle will return the initial value that you assigned it  SQL gt  drop sequence my number sn   Sequence dropped   SQL gt  create sequence my number sn start with 261   Sequence created   SQL gt   insert into my numbers my number  values  my number sn NEXTVAL    1 row created   If you re trying to do the  gapless  thing  I strongly advise you to 1 not do it  and  2 not use a sequence for it

User · Answer

DECLARE     v max NUMBER  BEGIN     SELECT  NVL  MAX   lt COLUMN NAME gt    0    1  INTO v max FROM  lt TABLE NAME gt       EXECUTE IMMEDIATE  CREATE SEQUENCE  lt SEQUENCE NAME gt  INCREMENT BY 1 START WITH      v max      NOCYCLE CACHE 20 NOORDER   END

User · Answer

You can t use a subselect inside a CREATE SEQUENCE statement  You ll have to select the value beforehand

User · Answer

Bear in mid  the MAX value will only be the maximum of committed values  It might return 1234  and you may need to consider that someone has already inserted 1235 but not committed

User · Answer

use dynamic sql   BEGIN             DECLARE             maxId NUMBER                BEGIN               SELECT MAX id  1               INTO maxId               FROM table name                          execute immediate  CREATE SEQUENCE sequane name MINVALUE    maxId    START WITH    maxId    INCREMENT BY 1 NOCACHE NOCYCLE                  END  END

User · Answer

If you can use PL SQL  try  EDIT  Incorporates Neil s xlnt suggestion to start at next higher value    SELECT  CREATE SEQUENCE transaction sequence MINVALUE 0 START WITH    MAX trans seq no  1    INCREMENT BY 1 CACHE 20    INTO v sql   FROM transaction log   EXECUTE IMMEDIATE v sql    Another point to consider  By setting the CACHE parameter to 20  you run the risk of losing up to 19 values in your sequence if the database goes down  CACHEd values are lost on database restarts  Unless you re hitting the sequence very often  or  you don t care that much about gaps  I d set it to 1   One final nit  the values you specified for CACHE and INCREMENT BY are the defaults  You can leave them off and get the same result

[sql] How to create an Oracle sequence starting with max value from a table?

Examples related to sql

Examples related to oracle

Examples related to sequence