I am trying to create a sequence that starts from the max id in my table.

I went off this previously asked question How to create sequence using starting value from query?

This is the sequence:

CREATE SEQUENCE q9_seq;

SELECT setval('q9_seq', (SELECT MAX(department_id) FROM hr_departments));

The result im getting is :

ORA-00923: FROM keyword not found where expected error.

2 Answers

1
Community On
declare
v_num number;
begin
    select nvl(max(value), 0) + 1
    into v_num
    from table;

    execute immediate 'create sequence drop_me_seq start with ' || v_num;
end;
/
1
Alex Poole On

You could use client-specific functionality like substitution variables for this, e.g. in SQL*Plus or SQL Developer:

column v_start new_value v_start

select nvl(max(department_id), 0) + 1 as v_start from hr_departments;

create sequence q9_seq start with &v_start;

old:create sequence q9_seq start with &v_start
new:create sequence q9_seq start with 43

Sequence Q9_SEQ created.

select q9_seq.nextval from dual;

   NEXTVAL
----------
        43

or you could alter an existing sequence to skip numbers, or call nextval until you reach the existing maximum (as here).

Or you could use a simple anonymous block:

declare
  l_start number;
begin
  select nvl(max(department_id), 0) + 1 into l_start from hr_departments;

  execute immediate 'create sequence q9_seq start with ' || l_start;
end;
/

PL/SQL procedure successfully completed.

select q9_seq.nextval from dual;

   NEXTVAL
----------
        43

The dynamic SQL statement is generated the same as with the substitution variable approach, as create sequence q9_seq start with 43.

(Both of these assume your table's highest current value is 42...)