Create a Sequence with START WITH from Query
32,244
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
Author by
Victor
I had worked as a Backend Software Engineer since 2007, mostly working with Java and C/C++.
Updated on July 05, 2022Comments
-
Victor almost 2 years
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
-
steven2308 over 8 yearsI had a little problem when the table is new because "max(id)" returns null. So I had to change the select to: "select nvl(max(id),0) + 1" Maybe someone else runs into the same problem