Create a Sequence with START WITH from Query
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
Here I have my example which works just fine:
declare ex number;begin select MAX(MAX_FK_ID) + 1 into ex from TABLE; If ex > 0 then begin execute immediate 'DROP SEQUENCE SQ_NAME'; exception when others then null; end; execute immediate 'CREATE SEQUENCE SQ_NAME INCREMENT BY 1 START WITH ' || ex || ' NOCYCLE CACHE 20 NOORDER'; end if;end;