Create a Sequence with START WITH from Query

Question

How can I create a Sequence where my START WITH value comes from a query?

I'm trying this way: CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table");

But, I get the ORA-01722 error

Answer 1

The START WITH CLAUSE accepts an integer. You can form the "Create sequence " statement dynamically and then execute it using execute immediate to achieve this.

declare
    l_new_seq INTEGER;
begin
   select max(id) + 1
       into l_new_seq
         from test_table;
   execute immediate 'Create sequence test_seq_2
                          start with ' || l_new_seq ||
                         ' increment by 1';
end;
/

Check out these links.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6014.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm