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?
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.