PL/SQL use a value declared for setting a sequence start

Question

I'm new in PL/SQL in Oracle and what I've tried so far doesn't work

I want to get the max(id) of my table... And then create a SEQUENCE which starts with this maxID+1

DECLARE maxId NUMBER;

BEGIN
  SELECT max(id)+1 INTO maxId FROM TABLE;
  CREATE SEQUENCE "DB"."SEQ_TABLE"  MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH maxId CACHE 20 NOORDER NOCYCLE;
END;
/

But I can't use CREATE here ...

How can I achieve this ?

Answer 1

Dynamic SQL as follows:

DECLARE 
  maxId NUMBER;
  v_sql varchar(200);
BEGIN
  SELECT max(ID) INTO maxId FROM MyTable;
  v_sql := 'CREATE SEQUENCE SEQ_TABLE  MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH '|| to_char(maxId) ||' CACHE 20 NOORDER NOCYCLE';
  execute immediate v_sql;
END;
/

All the edits...

Answer 2

You can try this :

 DECLARE
       maxId   NUMBER;
       v_sql   VARCHAR2 (200);
    BEGIN
       SELECT MAX (employee_id) + 1
         INTO maxId
         FROM employee;

         v_sql:= 'CREATE SEQUENCE SEQ_TABLE START WITH '|| maxId ||' INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999 NOORDER  NOCYCLE';        
        EXECUTE IMMEDIATE v_sql ;
    END;
    /

Answer 3

Thanks for your help but finnaly I've found my way !

I did like so :

DECLARE maxId NUMBER;

BEGIN
  SELECT max(id)+1 INTO maxId FROM TABLE;
  execute immediate 'CREATE SEQUENCE  "DB"."SEQ_TABLE"  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH '|| maxId ||' CACHE 20 NOORDER  NOCYCLE';
END;
  /