Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
MINVALUE 0
START WITH (SELECT MAX(reservation_id)
 FROM reservation) 
INCREMENT BY 1
CACHE 20;
share|improve this question
2  
'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
2  
See stackoverflow.com/questions/798766/… Did you copy this question? the wording looks strangely familiar ! –  Vincent Malgrat Feb 1 '13 at 9:00
add comment

2 Answers

up vote 5 down vote accepted

You cannot mix DDL with DML in this way.

Your best bet is an anonymous PL/SQL block:

BEGIN
DECLARE
seqval NUMBER;
  BEGIN
  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');
  END;
END;
/

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

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.