Oracle: Setting sequence to a specific value

Question

I am using liquibase to manage my schema. I have a bunch of insert statements. I have hard coded the primary-id numbers. After all my inserts are done, I want to change the sequence value to 1 more than the maximum value of the primary-key in the table. For this I wrote a PL/SQL as given below. However, when I execute select ArtifactTypes_id_seq.nextval from dual; it still increments 0.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
    sequence_id NUMBER;
BEGIN

    SELECT NVL(MAX(idArtifactType), 0) + 1 INTO sequence_id FROM ArtifactTypes;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY ' || sequence_id;
    EXECUTE IMMEDIATE 'SELECT ArtifactTypes_id_seq.nextval FROM dual';
    EXECUTE IMMEDIATE 'ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY 1';
    DBMS_OUTPUT.put_line('Executed "ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY ' || sequence_id || '"');
END;
/

The DBMS_OUTPUT gives following output -
Executed "ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY 71"

Anything that I am missing out? Am I doing the wrong way? NOte: I tried executing these SQLs from SQLDeveloper.

score 10 · Accepted Answer · answered Jul 26 '12 at 14:50

So I found the issue. Following should be the PL/SQL -

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
    sequence_id NUMBER;
    temp_seq NUMBER;
BEGIN
    SELECT NVL(MAX(idArtifactType), 0) + 1 INTO sequence_id FROM ArtifactTypes;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY ' || sequence_id;
    SELECT ArtifactTypes_id_seq.nextval into temp_seq FROM dual;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY 1';
    DBMS_OUTPUT.put_line('Executed "ALTER SEQUENCE saas.ArtifactTypes_id_seq INCREMENT BY ' || sequence_id || '"');
END;
/

Following statement -
EXECUTE IMMEDIATE 'SELECT ArtifactTypes_id_seq.nextval FROM dual';
was changed to
SELECT ArtifactTypes_id_seq.nextval into temp_seq FROM dual;

AND IT WORKED!!

score 7 · Answer 2 · answered Jul 26 '12 at 14:36

You need to drop the sequence and then re-create it, use the STARTS WITH clause to assign the initial value, eg:

CREATE SEQUENCE saas.ArtifactTypes_id_seq
  START WITH 72;

Also, selecting the NEXTVAL from a sequence automatically increments it by 1.

Oracle: Setting sequence to a specific value

2 Answers2