To change an existing sequence via SQLPlus :
SQLPLUS>CREATE SEQUENCE HIBERNATE_SEQUENCE START WITH 1000;
SQLPLUS>SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL;
NEXTVAL : 1000
SQLPLUS>ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 100;
SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL;
NEXTVAL : 1100
SQL>ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 1;
SQL>SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL;
NEXTVAL : 1101
This is how it should work. However, if I execute the following statements in order to dynamically do the same via EXECUTE IMMEDIATE in a stored procedure, the generated sequence (starting from 1000) is :
ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 100;
SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL;
ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 1;
SQL> SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL;
NEXTVAL : <b>1001</b>
Here is my package :
CREATE OR REPLACE
PACKAGE pkg_asktom AS
PROCEDURE alterSequenceLast(
sequenceName IN VARCHAR2,
inc in integer);
END;
/
CREATE OR REPLACE
PACKAGE BODY pkg_asktom AS
PROCEDURE alterSequenceLast(
sequenceName IN VARCHAR2,
inc in integer)
is
stmt VARCHAR2(2000);
begin
BEGIN
-- alter increment to inc
stmt := 'ALTER SEQUENCE ' || sequenceName ||' INCREMENT BY ' || inc;
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt;
-- read the next value
stmt := 'SELECT ' || sequenceName || '.NEXTVAL FROM DUAL';
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt;
-- alter increment to 1
stmt := 'ALTER SEQUENCE ' || sequenceName ||' INCREMENT BY 1';
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
END;
end alterSequenceLast;
END;
/
This is what I type in the sqlplus client :
SQL>create sequence HIBERNATE_SEQUENCE start with 1000;
SQL>set serveroutput on size 100000;
SQL> exec pkg_asktom.alterSequenceLast('H',100);
Executing 'ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 100'
Executing 'SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL'
Executing 'ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 1'
PL/SQL procedure successfully completed.
SQL> select H.nextval from dual;
NEXTVAL
----------
1000
SQL> select H.nextval from dual;
NEXTVAL
----------
1001
Thanks for your help.
your execute immediate of the select statement is a big "no operation", you didn't actually "into" it - so it was effectively ignored, you never fetched the value.
CREATE OR REPLACE
PACKAGE BODY pkg_asktom AS
PROCEDURE alterSequenceLast(
sequenceName IN VARCHAR2,
inc in integer)
is
stmt VARCHAR2(2000);
l_n number;
begin
BEGIN
-- alter increment to inc
stmt := 'ALTER SEQUENCE ' || sequenceName ||' INCREMENT BY ' ||
inc;
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt;
-- read the next value
stmt := 'SELECT ' || sequenceName || '.NEXTVAL FROM DUAL';
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt into l_n;
-- alter increment to 1
stmt := 'ALTER SEQUENCE ' || sequenceName ||' INCREMENT BY 1';
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt;
END;
end alterSequenceLast;
END;
/
Note that I fixed the egregious bug in your code - I removed the when others then null block - when others, not followed by raise or raise_application_error, is a bug in this code - there is no way to say it is not. Please do not use that construct, in 11g we actually WARN you at compile time that you have a bug - but just stop doing that. If you put a RAISE there, that'll be fine. If you just remove it - it will be even better in this case.
ops$tkyte%ORA10GR2> CREATE OR REPLACE
2 PACKAGE pkg_asktom AS
3 PROCEDURE alterSequenceLast(
4 sequenceName IN VARCHAR2,
5 inc in integer);
6 END;
7 /
Package created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE
2 PACKAGE BODY pkg_asktom AS
3 PROCEDURE alterSequenceLast(
4 sequenceName IN VARCHAR2,
5 inc in integer)
6 is
7 stmt VARCHAR2(2000);
8 l_n number;
9 begin
10 BEGIN
11 -- alter increment to inc
12 stmt := 'ALTER SEQUENCE ' || sequenceName ||' INCREMENT BY ' ||
13 inc;
14 dbms_output.put_line('Executing ''' || stmt || '''');
15 EXECUTE IMMEDIATE stmt;
16 -- read the next value
17 stmt := 'SELECT ' || sequenceName || '.NEXTVAL FROM DUAL';
18 dbms_output.put_line('Executing ''' || stmt || '''');
19 EXECUTE IMMEDIATE stmt into l_n;
20 -- alter increment to 1
21 stmt := 'ALTER SEQUENCE ' || sequenceName ||' INCREMENT BY 1';
22 dbms_output.put_line('Executing ''' || stmt || '''');
23 EXECUTE IMMEDIATE stmt;
24 EXCEPTION
25 WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
26 END;
27 end alterSequenceLast;
28 END;
29 /
Package body created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop sequence HIBERNATE_SEQUENCE;
Sequence dropped.
ops$tkyte%ORA10GR2> create sequence HIBERNATE_SEQUENCE start with 1000;
Sequence created.
ops$tkyte%ORA10GR2> set serveroutput on size 100000;
ops$tkyte%ORA10GR2> exec pkg_asktom.alterSequenceLast('HIBERNATE_SEQUENCE',100);
Executing 'ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 100'
Executing 'SELECT HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL'
Executing 'ALTER SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 1'
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select HIBERNATE_SEQUENCE.nextval from dual;
NEXTVAL
----------
1100
and that is the way to do a cut and paste - note that your example doesn't make sense.
You use H and HIBERNATE_SEQUENCE interchangeably. There is no H, you did not cut and paste, you made up output - which usually is just a waste of time (mine) as it doesn't represent reality.