how to reset sequence automatically to 1 every year ?

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;

Select allOpen in new window

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

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.

What does the code look like that inserts your id into the table and what is the sequence name?

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

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;
/

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

Can anyone help me to resolve this issue


Thanks

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")...

Sir post some example code


Thanks

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...

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.

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.

Thanks for all,  I have solved my problem my self



THANKS AGAIN
Hina mansoor

I've requested that this question be deleted for the following reason:

i have solved my problem my self

Can you post your solution for the knowledge base?

A mention of how you solved the problem would be useful to others ?

Thanks for all gurus

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;
------------------------------------------------------------------------------------------------