Home » Developer & Programmer » Forms » reset sequence (oracle Form)
reset sequence [message #604715] Fri, 03 January 2014 05:55 Go to next message
alhanashi
Messages: 7
Registered: December 2013
Junior Member
hi all

how to reset sequence automatically to 1 every year ?

example:
i use some id like 001/2013 , 002/2013 ... etc
and this it will chose from sequence and year, so if last number in 2013 like 125/2013 in new year it will take like 126/2014, how can automatic reset sequence to 1 so it will save like 001/2014

Re: reset sequence [message #604717 is a reply to message #604715] Fri, 03 January 2014 06:51 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
Try this its easy not much problem.

sql>create sequence seq;

sql>select seq.nextval from emp;

 NEXTVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14

sql>column S new_val inc;

sql>select seq.nextval S from dual;

         S
----------
        15

-----------------------
create or replace
  procedure reset_seq( p_seq_name in varchar2 )
  is
      l_val number;
  begin
      execute immediate
      'select ' || p_seq_name || '.nextval from dual' INTO l_val;

      execute immediate
      'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
                                                               ' minvalue 0';

      execute immediate
      'select ' || p_seq_name || '.nextval from dual' INTO l_val;

      execute immediate
      'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
  end;





http://www.dba-oracle.com/t_sequences.htm

http://stackoverflow.com/questions/10159840/need-to-reset-the-value-of-sequence-in-oracle

https://community.oracle.com/thread/1049407?tstart=0

http://www.orafaq.com/wiki/Sequence

http://jaredstill.com/content/reset-sequence.html

http://psoug.org/reference/sequences.html

Regard
Mughal

[Updated on: Fri, 03 January 2014 07:05]

Report message to a moderator

Re: reset sequence [message #604747 is a reply to message #604717] Fri, 03 January 2014 12:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where do both parts of the "sequence number" come from, i.e. what generates 001, 002, etc. and what generates 2013, 2014 etc.? How do these two parts come together and make 001/2013 or 002/2013?
Re: reset sequence [message #604974 is a reply to message #604747] Mon, 06 January 2014 11:54 Go to previous message
sreekumar.nair.it
Messages: 16
Registered: January 2014
Junior Member
Hi,

I am able to achieve this requirement using my workaround i.e. without using sequence. Please follow the steps mentioned below:

1)Create a table for testing purpose
create table TEMP_SEQUENCE
(SEQ_VAL VARCHAR2(100));

2) Create function using attached text file

3) Test Scenario 1
insert into temp_sequence values ('00001/2013')

4) Execute below select
select auto_year_seq_func from dual

5) Test Scenario 2
insert into temp_sequence values ('00001/2014')

6) Execute below select
select auto_year_seq_func from dual

Hope my workaround solves your purpose. Smile

Regards,
Sreee
Previous Topic: tree not execute the query
Next Topic: How To implement jre instead of jinit
Goto Forum:
  


Current Time: Thu Mar 28 09:45:48 CDT 2024