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

how to reset sequence automatically to 1 every year ?

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
hinamansoor
Asked:
hinamansoor
  • 8
  • 3
  • 3
  • +4
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
alcindorCommented:
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
 
Greg CloughSenior Oracle DBACommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
awking00Commented:
What does the code look like that inserts your id into the table and what is the sequence name?
0
 
hinamansoorAuthor Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
hinamansoorAuthor Commented:
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
 
hinamansoorAuthor Commented:
Can anyone help me to resolve this issue


Thanks
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
hinamansoorAuthor Commented:
Sir post some example code


Thanks
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
hinamansoorAuthor Commented:
Thanks for all,  I have solved my problem my self



THANKS AGAIN
Hina mansoor
0
 
hinamansoorAuthor Commented:
I've requested that this question be deleted for the following reason:

i have solved my problem my self
0
 
awking00Commented:
Can you post your solution for the knowledge base?
0
 
alcindorCommented:
A mention of how you solved the problem would be useful to others ?
0
 
hinamansoorAuthor Commented:
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
 
hinamansoorAuthor Commented:
Thanks for all gurus
0
 
Sky SenderCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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