Solved

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

Posted on 2014-09-08
11
791 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40310682
dvz,

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

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 35

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 35

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

730 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