sql - How to create an Oracle sequence starting with max value from a table?

Question

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work?

CREATE SEQUENCE transaction_sequence
  MINVALUE 0
  START WITH (SELECT MAX(trans_seq_no)
     FROM TRANSACTION_LOG) 
  INCREMENT BY 1
  CACHE 20;

Answer 1

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.