Solved

How do I automatically reset a sequence's value to 0 every  month

Posted on 2014-09-08
11
472 Views
Last Modified: 2014-11-17
Hi Expert,
below is my function

Function  Ticket_id (s_id IN NUMBER)
  RETURN VARCHAR2
IS
    
    p_id      varchar2(2);
    strmonth      varchar2(6);
    retval varchar2(15);
begin
    select id_prefix into p_id from ticket_types_tty where t_type_id=s_id;
    select TO_CHAR(sysdate,'yyyymm') into STRMONTH from DUAL;
    select (p_id||strmonth||seq_a.nextval) into retval from Ticket_tab where ticket_id like '%'||strmonth||'%%';
    return retval;
END ticket_id;

Open in new window


on above function i am generating  ticket id, but i want to reset the sequence every month.

suppose on   month of september sequence is like  below
ER2014090001
ER2014090002
ER2014090003............
expected:
 on next  month it would be like this
ER2014100001
ER2014100002

Regards
Thomos
0
Comment
Question by:deve_thomos
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 23

Expert Comment

by:David
ID: 40310536
seq_a has to be dropped, then re-created, in order to force the value back to one.
0
 

Author Comment

by:deve_thomos
ID: 40310545
Hello DVZ
I do not want to drop manually ,  how do i do automatically?

Regards
Thomos
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40310549
Drop and recreate is one way.

Another way is to set the increment by to a negative number that would get you back to 1, get the next value, then set the increment back to 1.  There is a few different methods to do that in this Ask Tom article ->  https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597

In your case, why do you start at 1 each month?  Why not let the sequence handle the date?  Have the sequence start at 2014090001.  Saves you a few steps in generating the number, then you only need to get next value.  At the end of the month, you don't put in a negative to get it to the next months value, you put in the positive number to get you to the next months value.  Same idea though.
0
 
LVL 23

Expert Comment

by:David
ID: 40310581
Or, we can all learn a new trick from : http://www.oratable.com/reset-sequence-in-oracle/
0
 

Author Comment

by:deve_thomos
ID: 40310590
Ok Dvz,
i under stood, but  every moth how do i will drop that sequence and create again.

it should be automatically.

Regards
Thomos
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 34

Expert Comment

by:johnsone
ID: 40310682
dvz,

That is directly from the Tom Kyte article I posted.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40312672
The article from Tom Kyte that johnsone posted shows you the SQL commands required to do this.  The article that dvx posted takes this one step further, and puts those SQL statements into a PL\SQL procedure.  This allows you to re-set a sequence whenever you want to *WITHOUT* having to drop and re-create it.  If you simply schedule that procedure to run at midnight on the first day of each month, that should give you what you are asking for.
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 40316458
I agree with the experts comments , the simpler way is to drop and recreate.

Though, if you still wants a solution for this is, I suggest you see below for a method and its illustration

1. Pre-Create sequences based on the month name and date
2. Wrap a function and select the value of the sequence from the function itself rather than the sequence

Eg:

create sequence SEP_01_2014
MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Open in new window



this will create a sequence for the current month(Sep-2014) like this you have to pre-create for a long run (say 2 years or so), the next month sequence would be OCT_01_2014 and so on.

Create a function which will dynamically select the sequence value based on the date and return the sequence value

CREATE or replace FUNCTION seq_val_return
   RETURN NUMBER
IS
   var_num   NUMBER;
   var_seq   varchar2(100);
   var_sql   VARCHAR2 (1000);
BEGIN
   SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'MON_DD_YYYY')
     INTO var_seq
     FROM DUAL;

   var_sql := 'select ' || var_seq || '.nextval' || ' from dual';

   EXECUTE IMMEDIATE var_sql
                INTO var_num;

   RETURN var_num;
END;

Open in new window


verify the results,,


select seq_val_return from dual

this way you could always reset the sequence value to start from 1 though, technically you are not resetting though.. This is one of the option if you don't want to drop and recreate..
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40316474
I re-read your question once more...apologies for missing it in the first ..

you could use the logic and embed it in your function too(provided that you had created the function and sequences mentioned in my above comment before hand)..


create or replace Function  Ticket_id (s_id IN NUMBER)
  RETURN VARCHAR2
IS
   
    p_id      varchar2(2);
    strmonth      varchar2(6);
    retval varchar2(15);
begin
    select id_prefix into p_id from ticket_types_tty where t_type_id=s_id;
    select TO_CHAR(sysdate,'yyyymm') into STRMONTH from DUAL;
    select (p_id||strmonth||seq_val_return) into retval from Ticket_tab where ticket_id like '%'||strmonth||'%%';
    return retval;
END ticket_id;
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40316673
Drop and create may be the "easier" way to go, but you have to worry about privileges and doing so will also invalidate all dependent objects.  This could lead to problems (especially if triggers reference the sequence).  If the system is quiet enough at the switch, or done during a maintenance window, it wouldn't be a problem, but the potential is there for users/processes to get errors.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40316821
I just re-read the question and I see problems with these two queries in your function:

select TO_CHAR(sysdate,'yyyymm') into STRMONTH from DUAL;
select (p_id||strmonth||seq_a.nextval) into retval from Ticket_tab where ticket_id like '%'||strmonth||'%%';

You don't need the first "select ... frm dual;".  That syntax was required in the first version of PL\SQL back in the Oracle7 database days (about 20 years ago).  Change that to a simple (and more efficient) PL\SQL assignment statement instaed like this:
  STRMONTH := TO_CHAR(sysdate,'yyyymm');

This part of that second query: "where ticket_id like '%'||strmonth||'%%';" will make this query slower and slower as records are added to the Ticket_tab table, since this will force a full-table scan (unless you have a function-based index that exactly matches this syntax).   You aren't even returning anything from the Ticket_tab table in this query, so why even reference it?  Using the dual table would be better here.  (You do need a select from some table to get the NEXTVAL in a PL\SQL function or procedure, unless you use NEXTVAL directly in an insert statement.)
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

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

21 Experts available now in Live!

Get 1:1 Help Now