Based on Ivan Laharnar with less code and simplier:
declare
lastSeq number;
begin
SELECT MAX(ID) + 1 INTO lastSeq FROM <TABLE_NAME>;
if lastSeq IS NULL then lastSeq := 1; end if;
execute immediate 'CREATE SEQUENCE <SEQUENCE_NAME> INCREMENT BY 1 START WITH ' || lastSeq || ' MAXVALUE 999999999 MINVALUE 1 NOCACHE';
end;
Bear in mid, the MAX value will only be the maximum of committed values. It might return 1234, and you may need to consider that someone has already inserted 1235 but not committed.
use dynamic sql
BEGIN
DECLARE
maxId NUMBER;
BEGIN
SELECT MAX(id)+1
INTO maxId
FROM table_name;
execute immediate('CREATE SEQUENCE sequane_name MINVALUE '||maxId||' START WITH '||maxId||' INCREMENT BY 1 NOCACHE NOCYCLE');
END;
END;
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;
You can't use a subselect inside a CREATE SEQUENCE
statement. You'll have to select the value beforehand.
If you can use PL/SQL, try (EDIT: Incorporates Neil's xlnt suggestion to start at next higher value):
SELECT 'CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH '||MAX(trans_seq_no)+1||' INCREMENT BY 1 CACHE 20'
INTO v_sql
FROM transaction_log;
EXECUTE IMMEDIATE v_sql;
Another point to consider: By setting the CACHE parameter to 20, you run the risk of losing up to 19 values in your sequence if the database goes down. CACHEd values are lost on database restarts. Unless you're hitting the sequence very often, or, you don't care that much about gaps, I'd set it to 1.
One final nit: the values you specified for CACHE and INCREMENT BY are the defaults. You can leave them off and get the same result.
DECLARE
v_max NUMBER;
BEGIN
SELECT (NVL (MAX (<COLUMN_NAME>), 0) + 1) INTO v_max FROM <TABLE_NAME>;
EXECUTE IMMEDIATE 'CREATE SEQUENCE <SEQUENCE_NAME> INCREMENT BY 1 START WITH ' || v_max || ' NOCYCLE CACHE 20 NOORDER';
END;
Source: Stackoverflow.com