Solved

how to reset sequence automatically to 1 every year ?

Posted on 2014-01-03
20
3,246 Views
Last Modified: 2016-08-18
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.

Thanks
Hina mansoor
0
Comment
Question by:hinamansoor
  • 8
  • 3
  • 3
  • +4
20 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39753671
This one's from http://www.oratable.com/reset-sequence-in-oracle/

...but there are numerous examples out there, just aks google ;-)

You may call this proc then from within your DBMS_JOB or scheduler to perform annual the annual reset...

create or replace
procedure reset_sequence(p_seq in varchar2)
is
    l_value number;
begin
-- Select the next value of the sequence
 
    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;
 
-- Set a negative increment for the sequence,
-- with value = the current value of the sequence
 
    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by -' || l_value || ' minvalue 0';
 
-- Select once from the sequence, to
-- take its current value back to 0
 
    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;
 
-- Set the increment back to 1
 
    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by 1 minvalue 0';
end;

Open in new window

0
 
LVL 2

Expert Comment

by:alcindor
ID: 39754011
I use Microsoft SQL Server 2005. The following might help you, you will need to make modifications to suit your application :

You can use a user define function to create the ID.
As an example, create a new table as follows, the TableID column is the one that you wish to act as the sequence in you question:


CREATE TABLE [dbo].[t1](
      [TableID]  char(10) NULL,
      [name] [char](40) NULL,
      [Address] [char](70) NULL
) ON [PRIMARY]


Then create a function as follows:

CREATE FUNCTION Calc_t1_ID
(
)
RETURNS char(10)
AS
BEGIN
      -- Declare the return variable here
      declare @result char(10)
      declare @thisyear char(4)
      declare @id char(3)
      declare @n int

      select @thisyear = Datepart(yyyy,GetDate())

      if exists(select * from t1 where TableID like '%' + @thisyear)
      begin
            select @id = substring(max(Tableid),1,3)
            from t1
            where TableID like '%' + @thisyear
            set @n = convert(int,@id)+1
            set @id = RIGHT( '00' + convert(varchar(3),@n),3)
      end
      else
      begin
            -- make a new series of id values for new year
            set @id =  '001'  
      end
      set @result = @id + '/' + @thisyear
      return @result
END
GO

Finally, Test the idea by running the following query which
appends a new record to the table and displays the table content;


insert t1 (TableID,[name],[Address])
VALUES(dbo.Calc_t1_ID(),'name 2','Address 2')
select * from t1
0
 
LVL 6

Expert Comment

by:Greg Clough
ID: 39754366
It depends on how accurate you need to be, and what your transaction volume is like.  If you can reasonably guarantee that nothing is running at midnight on 31/DEC that's using the sequence, then resetting it as advised by Alex140181 is the cleanest solution... and one that I've used myself many times.  Keep in mind that if something happens to slip in and select a sequence value in the middle of the procedure then you could either end up with a negative sequence value, or something that doesn't start at 1.  Your code needs to handle both scenarios.

If you need to absolutely 100% guarantee that it starts at 1, then your options are:

1) Drop and re-create the sequence.  This means you need to re-grant any permissions, and for the time that you run the script any transactions that try and use it will fail. :-O

2) Have a new sequence for each year, and when your program flips over to 2014 then have it use the new sequence.  (e.g. MY_SEQUENCE_2013, MY_SEQUENCE_2014, MY_SEQUENCE_2015, etc.)  This means you have a ticking time-bomb, and if you ever forget to create enough new sequences for the future years then things will break.  Maybe code the creation of the new sequence to run automatically in December each year to make it self-maintaining.

Neither of these are really nice in my view, but until Oracle lets us do an atomic operation that will reset a sequence to a specific value then I don't know of any elegant alternative if you need a 100% guarantee.

Regards.
Greg.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39754504
What does the code look like that inserts your id into the table and what is the sequence name?
0
 

Author Comment

by:hinamansoor
ID: 39755764
Exactly i want this can anybody change this code to oracle forms

CREATE FUNCTION Calc_t1_ID
(
)
RETURNS char(10)
AS
BEGIN
      -- Declare the return variable here
      declare @result char(10)
      declare @thisyear char(4)
      declare @id char(3)
      declare @n int

      select @thisyear = Datepart(yyyy,GetDate())

      if exists(select * from t1 where TableID like '%' + @thisyear)
      begin
            select @id = substring(max(Tableid),1,3)
            from t1
            where TableID like '%' + @thisyear
            set @n = convert(int,@id)+1
            set @id = RIGHT( '00' + convert(varchar(3),@n),3)
      end
      else
      begin
            -- make a new series of id values for new year
            set @id =  '001'  
      end
      set @result = @id + '/' + @thisyear
      return @result
END
GO


Thanks
Hina mansoor
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 39756024
CREATE OR REPLACE FUNCTION F_Getnewid (TABLE_NAME VARCHAR2)
   RETURN INTEGER
IS
   NEW_ID               INTEGER;
   I_CURRENT_YR         INTEGER;
   I_TRANSACTION_YEAR   INTEGER;
   I_YEAR_DIFF          INTEGER;
BEGIN
   SELECT   NVL ("COLUMN_ID", 0)
     INTO   I_TRANSACTION_YEAR
     FROM   T1
    WHERE   T1.TABLEID = TABLE_NAME;

   I_CURRENT_YR := TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));


   I_YEAR_DIFF := I_CURRENT_YR-I_TRANSACTION_YEAR;

   IF I_YEAR_DIFF = 0
   THEN
      EXECUTE IMMEDIATE 'SELECT SEQ_' || TABLE_NAME || '.NEXTVAL FROM DUAL'
         INTO   NEW_ID;
   ELSE
      reset_sequence ('SEQ_' || TABLE_NAME, NEW_ID);

      -- U CAN USE IT AS MENTIONED IN FIRST POST ONLY YOU NEED TO ADD AN OUT PARAMETER

      UPDATE   T1
         SET   COLUMN_ID = I_CURRENT_YR
       WHERE   T1.TABLEID = TABLE_NAME;
   END IF;

   RETURN NEW_ID;
END F_Getnewid;
/
0
 

Author Comment

by:hinamansoor
ID: 39756405
Sir thanks for your prompt reply i tried this above code but giving me error

"Warning: Function created with compilation errors."


SQL> show error
Errors for FUNCTION F_GETNEWID:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/4      PL/SQL: SQL Statement ignored
9/18     PL/SQL: ORA-00904: "COLUMN_ID": invalid identifier
20/7     PL/SQL: Statement ignored
20/7     PLS-00201: identifier 'RESET_SEQUENCE' must be declared
22/7     PL/SQL: SQL Statement ignored
23/16    PL/SQL: ORA-00904: "COLUMN_ID": invalid identifier
SQL>



Thanks
0
 

Author Comment

by:hinamansoor
ID: 39757822
Can anyone help me to resolve this issue


Thanks
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39757872
That was a quite unqualified and untested post by shajukg.
"reset_sequence" should be a procedure (to be created & compiled beforehand) that resets a given sequence. You have to adept the code to fit your table(s) naming conventions (as you won't have a table t1 with columns like "column_id")...
0
 

Author Comment

by:hinamansoor
ID: 39757883
Sir post some example code


Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39757910
Exactly i want this can anybody change this code to oracle forms

This is what you asked for and I am not going to do that for you. That way, you won't learn anything! If you're completely new to Oracle PL/SQL, I suggest you begin with some basic stuff and no, I will not give you basic examples as they are easily to find by yourself (numerous samples) -> google ;-)
I / we showed you a way to reset Oracle sequences, but  it's really not up to me (us) to do your work...
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 39758723
Reset_sequence is the procedure as mentioned in the very first reply by "Alex" on your post.  you need to create it first before running the function. Also the "column_id" is the column where the information about transaction year is stored in t1 table. the column name might be different in your case , please change accordingly.
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 39758740
eg:  for invoice transaction

t1 table will have the following values

Table_Name: INVOICE
column_id: 2013

when you call the the getnewid() function in very first transaction of 2014 , the difference in year stored in lookup table triggers reset sequence and updates the look up year value ..ie column id to 2014.
0
 

Author Comment

by:hinamansoor
ID: 39761558
Thanks for all,  I have solved my problem my self



THANKS AGAIN
Hina mansoor
0
 

Author Comment

by:hinamansoor
ID: 39762018
I've requested that this question be deleted for the following reason:

i have solved my problem my self
0
 
LVL 31

Expert Comment

by:awking00
ID: 39762008
Can you post your solution for the knowledge base?
0
 
LVL 2

Expert Comment

by:alcindor
ID: 39762019
A mention of how you solved the problem would be useful to others ?
0
 

Accepted Solution

by:
hinamansoor earned 0 total points
ID: 39764889
Sure i have solved my problem in this way.

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

2) Create function.

create or replace function AUTO_YEAR_SEQ_FUNC
return varchar2
is
l_ret_val VARCHAR2(100);
l_existing_max_year NUMBER;
l_current_year NUMBER;
l_val VARCHAR2(100);
l_col_size NUMBER;
begin
select data_length-5
into l_col_size
from user_tab_columns where table_name='TEMP_SEQUENCE'
and  column_name='SEQ_VAL';

  select max(to_number(substr(seq_val,-4)))
  into l_existing_max_year
  from temp_sequence;

  select to_number(to_char(sysdate,'YYYY'))
  into l_current_year
  from dual;

  if l_existing_max_year=l_current_year then
    select lpad((max(to_number(substr(seq_val,0,instr(seq_val,'/',1)-1)))+1),l_col_size,'0')
    into l_val
    from temp_sequence
    where substr(seq_val,-4)=to_char(l_current_year);
  else
    select lpad((max(to_number(substr(seq_val,0,instr(seq_val,'/',1)-1)))+1),l_col_size,'0')
    into l_val
    from temp_sequence
    where substr(seq_val,-4)=to_char(l_current_year);
  end if;
    if l_val is null then
    select lpad(1,l_col_size,'0')
    into l_val
    from dual;
    end if;
  l_ret_val:=l_val||'/'||l_current_year;
  return l_ret_val;
end;


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

Thanks
Hina mansoor
0
 

Author Closing Comment

by:hinamansoor
ID: 39776059
Thanks for all gurus
0
 

Expert Comment

by:Sky Sender
ID: 41761518
I've used a different solution.  I dynamically create new sequences daily...


CREATE OR REPLACE FUNCTION           appcustom.GEN_NEW_DBUI (
i_seq_prefix                        IN      VARCHAR2    DEFAULT 'DBUI'
)
RETURN VARCHAR2
------------------------------------------------------------------------------------------------
IS
    -- ** COMMENTS/NOTES: **********************************************************************
    --
    -- Generate a new Global Surrogate Key
    --
    -- *****************************************************************************************

    -- Declare a user defined exception
    EX_SEQ_NOT_FOUND                    EXCEPTION;    
    PRAGMA EXCEPTION_INIT (EX_SEQ_NOT_FOUND, -2289); --bind the error code to the above

    l_today_seq                             VARCHAR2 (32);

    l_seq_prefix                            VARCHAR2 (32);
    l_seq_no                                NUMBER;
    l_seq_id                                VARCHAR2 (32);

    l_sql                                   VARCHAR2 (32000);

------------------------------------------------------------------------------------------------
BEGIN
------------------------------------------------------------------------------------------------
   
    -- Buld the sequence for today.
    l_today_seq := 'SEQ_DBUI_' || TO_CHAR (SYSDATE, 'yyyymmdd');
   
    -- Try to Get the next Sequence Value
    BEGIN
        -- Bld Query
        l_sql := 'select appcustom.' || l_today_seq || '.NEXTVAL from dual';
        dbms_output.put_line(l_sql);
        EXECUTE IMMEDIATE l_sql INTO l_seq_no;

    EXCEPTION
        WHEN  EX_SEQ_NOT_FOUND THEN
       
            -- Create a new Sequence
            l_sql := 'CREATE SEQUENCE appcustom.' || l_today_seq;
            dbms_output.put_line(l_sql);
            EXECUTE IMMEDIATE l_sql;

            -- Grant Access
            l_sql := 'GRANT ALL ON appcustom.' || l_today_seq || ' TO PUBLIC';
            EXECUTE IMMEDIATE l_sql;

            -- Loop through old Date Based Sequences and DROP
            FOR dbs IN
            (
             SELECT sequence_name
               FROM dba_sequences
              WHERE sequence_owner = 'APPCUSTOM'
                AND sequence_name LIKE 'SEQ_DBUI_%'
                AND sequence_name != l_today_seq
            ) LOOP
           
                -- Delete Old Sequences
                l_sql := 'DROP SEQUENCE ' || dbs.sequence_name;
                dbms_output.put_line(l_sql);
                EXECUTE IMMEDIATE l_sql;

            END LOOP;

            -- Get new value
            l_sql := 'select appcustom. ' || l_today_seq || ' .NEXTVAL from dual';
            dbms_output.put_line(l_sql);
            EXECUTE IMMEDIATE l_sql INTO l_seq_no;
           
    END;            

    dbms_output.put_line('SEQ NO: ' ||l_seq_no );        
   
    -- Build new Prefix - Max length 4, but trim to value given
    l_seq_prefix := TRIM (SUBSTR (NVL (i_seq_prefix, 'DBUI'), 1, 4));

    -- Build final ID
    l_seq_id :=  l_seq_prefix || TO_CHAR (SYSDATE, 'YYYYMMDD') || TRIM (TO_CHAR (l_seq_no, '0000000000'));

    RETURN l_seq_id;

------------------------------------------------------------------------------------------------
EXCEPTION
------------------------------------------------------------------------------------------------

    WHEN OTHERS THEN

        RETURN -1;

------------------------------------------------------------------------------------------------
END GEN_NEW_DBUI;
------------------------------------------------------------------------------------------------
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Bulk insert into global temporary table 2 41
Oracle Pivot 2 35
Oracle Syntax 8 42
Clone Oracle 12c Database 5 27
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now