• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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?
0
anumoses
Asked:
anumoses
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
The only way I can think of to do this is create a database job that runs once a year.
0
 
anumosesAuthor Commented:
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;
0
 
anumosesAuthor Commented:
But I want to set it to 1000
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
slightwv (䄆 Netminder) Commented:
>>What do you say about this?

Is there is reason you just don't want to drop/recreate it?

The code you posted is basically doing:
get current value, alter the sqeunece to ust the negative of it, getting the next sequence (resetting to whatever you want), then incrementing by 1 again.

the problem here:  What if another transaction selects nextval while you are doing all this?

>>But I want to set it to 1000

Just don't reset the sequence to the 'current value'.  Set the 'increment by' to 'current value'-1000.
0
 
anumosesAuthor Commented:
Every year we wanted to start the control no with 1000.
0
 
slightwv (䄆 Netminder) Commented:
>>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

0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now