Oracle: Setting sequence to a specific value [duplicate]

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.

Answer 1

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!!

Answer 2

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.