Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

CREATE SEQUENCE reservation_sequence
START WITH (SELECT MAX(reservation_id)
 FROM reservation) 
share|improve this question
'This does not work' is hardly an error code in any system. What's more, it does not describe your problem at all. Do you get an error? Or you get a sequence with the wrong values? Or? – dezso Feb 1 '13 at 8:29
See… Did you copy this question? the wording looks strangely familiar ! – Vincent Malgrat Feb 1 '13 at 9:00
up vote 6 down vote accepted

You cannot mix DDL with DML in this way.

Your best bet is an anonymous PL/SQL block:

seqval NUMBER;
  SELECT MAX(nvl(reservation_id),0)
  INTO seqval
  FROM reservation;

  execute immediate('CREATE SEQUENCE reservation_sequence MINVALUE 0 START WITH '||seqval||' INCREMENT BY 1 CACHE 20');

Plus, are you sure you want max() and not max()+1?

share|improve this answer

In the Oracle SQL Manual you can find a syntax diagram

CREATE SEQUENCE [ schema. ] sequence
   [ { INCREMENT BY | START WITH } integer
   | { MAXVALUE integer | NOMAXVALUE }
   | { MINVALUE integer | NOMINVALUE }
   | { CYCLE | NOCYCLE }
   | { CACHE integer | NOCACHE }
   | { ORDER | NOORDER }

so START WITH must be followed by an integer and nothing else. An integer is also difined by a syntax diagram in this manual (to find the syntax diagram for integers look at the index of the manual

[ + | - ] digit [ digit ]...

The error you will get will be

ORA-01722: invalid number

The description of the error can be found in one of the manuals, too.

ORA-01722: invalid number
Cause: The specified number was invalid.
Action: Specify a valid number.

If you look at your error it may be that also the exact position where the error occurs in the statement may be displayed.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.