Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

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,'FM999999');

Everything works fine. My question is beginning of every year can I make the sequence restart from 1000? Is this possible?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

The only way I can think of to do this is create a database job that runs once a year.
Avatar of anumoses

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;
But I want to set it to 1000
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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;

Open in new window

thanks