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.
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;
/
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...)
