anumoses
asked on
Oracle question
I have a sequence created for control no like a confirmation number for a lor release performed.
This is my script.
DROP SEQUENCE HBC_DATA.CONTROL_NO_SEQ;
CREATE SEQUENCE HBC_DATA.CONTROL_NO_SEQ
START WITH 1000
MAXVALUE 1000000
MINVALUE 0
NOCYCLE
CACHE 20
NOORDER;
CREATE PUBLIC SYNONYM CONTROL_NO_SEQ FOR HBC_DATA.CONTROL_NO_SEQ;
GRANT ALTER, SELECT ON HBC_DATA.CONTROL_NO_SEQ TO HBC_PROC;
GRANT ALTER, SELECT ON HBC_DATA.CONTROL_NO_SEQ TO HBC_USER;
select CONTROL_NO_SEQ.nextval into v_control_no from dual;
v_control_no := to_char(sysdate,'mmddyy')| | to_char(v_control_no,'FM99 9999');
Everything works fine. My question is beginning of every year can I make the sequence restart from 1000? Is this possible?
This is my script.
DROP SEQUENCE HBC_DATA.CONTROL_NO_SEQ;
CREATE SEQUENCE HBC_DATA.CONTROL_NO_SEQ
START WITH 1000
MAXVALUE 1000000
MINVALUE 0
NOCYCLE
CACHE 20
NOORDER;
CREATE PUBLIC SYNONYM CONTROL_NO_SEQ FOR HBC_DATA.CONTROL_NO_SEQ;
GRANT ALTER, SELECT ON HBC_DATA.CONTROL_NO_SEQ TO HBC_PROC;
GRANT ALTER, SELECT ON HBC_DATA.CONTROL_NO_SEQ TO HBC_USER;
select CONTROL_NO_SEQ.nextval into v_control_no from dual;
v_control_no := to_char(sysdate,'mmddyy')|
Everything works fine. My question is beginning of every year can I make the sequence restart from 1000? Is this possible?
The only way I can think of to do this is create a database job that runs once a year.
ASKER
Correct. I found this online. What do you say about this?
declare
pragma autonomous_transaction;
ln_increment number;
ln_curr_val number;
ln_reset_increment number;
ln_reset_val number;
begin
-- save the current INCREMENT value for the sequence
select increment_by
into ln_increment
from user_sequences
where sequence_name = 'MY_SEQ';
-- determine the increment value required to reset the sequence
-- from the next fetched value to 0
select -1 - MY_SEQ.nextval into ln_reset_increment from dual;
-- fetch the next value (to make it the current value)
select MY_SEQ.nextval into ln_curr from dual;
-- change the increment value of the sequence to
EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
|| ln_reset_increment ||' minvalue 0';
-- advance the sequence to set it to 0
select MY_SEQ.nextval into ln_reset_val from dual;
-- set increment back to the previous(ly saved) value
EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
|| ln_increment ;
end;
declare
pragma autonomous_transaction;
ln_increment number;
ln_curr_val number;
ln_reset_increment number;
ln_reset_val number;
begin
-- save the current INCREMENT value for the sequence
select increment_by
into ln_increment
from user_sequences
where sequence_name = 'MY_SEQ';
-- determine the increment value required to reset the sequence
-- from the next fetched value to 0
select -1 - MY_SEQ.nextval into ln_reset_increment from dual;
-- fetch the next value (to make it the current value)
select MY_SEQ.nextval into ln_curr from dual;
-- change the increment value of the sequence to
EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
|| ln_reset_increment ||' minvalue 0';
-- advance the sequence to set it to 0
select MY_SEQ.nextval into ln_reset_val from dual;
-- set increment back to the previous(ly saved) value
EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
|| ln_increment ;
end;
ASKER
But I want to set it to 1000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Every year we wanted to start the control no with 1000.
>>Every year we wanted to start the control no with 1000.
I understand that.
Here is what that code is doing in a working test case. I still say this can be dangerous if there are active transactions while you are altering the sequence.
I understand that.
Here is what that code is doing in a working test case. I still say this can be dangerous if there are active transactions while you are altering the sequence.
drop sequence myseq;
create sequence myseq start with 1000;
-- get the nextval 1100 times
select myseq.nextval from xmltable('1 to 1100');
--now the magic from the link you found but tweaked
declare
next_val number;
begin
-- this should get 2100.
select myseq.nextval into next_val from dual;
-- set the increment by to -1001
execute immediate 'alter sequence myseq increment by ' || (next_val-999)*-1;
-- get the 'next' val (2100 - 1101) therefore 999
select myseq.nextval into next_val from dual;
--reset it to increment by 1 so the 'next' one will be 1000
execute immediate 'alter sequence myseq increment by 1';
end;
/
--make sure it worked
select myseq.nextval from dual;
ASKER
thanks