Unable to dynamically start sequence

Question

Based on this example, I created this code:

DECLARE
    new_employee_id INTEGER;
BEGIN
    SELECT MAX(EMP_ID) + 1 INTO new_employee_id FROM EMPLOYEE;
    EXECUTE IMMEDIATE 'CREATE OR REPLACE SEQUENCE primary_key_seq START WITH ' || new_employee_id || ' INCREMENT BY 1';
END;
/

OBS: perhaps using a sequence to auto increment the primary key of a table doesn't make much sense, but this is a school exercise and that's what we're supposed to do, so please disregard that aspect.

This is the error I have: ERROR AT LINE 1 ORA-00922: missing or invalid option ORA-06512: at line 5

What could be wrong?

score 1 · Answer 1 · answered Apr 04 '14 at 11:35

there is no REPLACE in create sequence command check documentation (wich is always a good idea ;-)

should be

DECLARE
    new_employee_id INTEGER;
BEGIN
    SELECT MAX(EMP_ID) + 1 INTO new_employee_id FROM EMPLOYEE;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE primary_key_seq START WITH ' || new_employee_id || ' INCREMENT BY 1';
END;
/

score 1 · Accepted Answer · answered Apr 04 '14 at 11:36

1

There is no CREATE OR REPLACE SEQUENCE option, so you must use the CREATE SEQUENCE Statement and the a DROP statement if you need to drop it first.

DECLARE
    new_employee_id INTEGER;
BEGIN
    SELECT MAX(EMP_ID) + 1 INTO new_employee_id FROM EMPLOYEE;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE primary_key_seq START WITH ' || new_employee_id || ' INCREMENT BY 1';
END;
/

answered Apr 04 '14 at 11:36

Luke

283
2
10

OMG... how could I not have see that :-) Thanks. – PrincessLilly Apr 04 '14 at 11:52

score 0 · Answer 3 · answered Apr 04 '14 at 12:03

This question begs another question - why are you trying to drop and re-add a sequence? You should never need to do this. Create the sequence once:

CREATE SEQUENCE PRIMARY_KEY_SEQ
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

Then, use it in your code:

DECLARE
  new_employee_id INTEGER;
BEGIN
  new_employee_id := PRIMARY_KEY_SEQ.NEXTVAL;

  INSERT INTO SOME_TABLE_MAYBE_EMPLOYEES
    (ID, WHATEVER, WHATEVER_ELSE)
  VALUES
    (new_employee_id, 'WHATEVER', 'WHATEVER_ELSE');
END;

The whole purpose of the sequence is to provide unique, non-repeating values. Going out to the table, grabbing the MAX(ID), and recreating the sequence to start from there is sort of backwards from the way a sequence should be used. Create a sequence once, then use it many times.

Hopefully this helps.

Share and enjoy.

I've been doing a lot of trial and error in order to figure out this exercise, that's why I needed to ensure a previously created sequence was dropped before I proceed. Please don't interpret my code as a real life example, it's just a school exercise. — PrincessLilly, Apr 04 '14 at 12:44

Unable to dynamically start sequence

3 Answers3