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

seq_a has to be dropped, then re-created, in order to force the value back to one.

Hello DVZ
I do not want to drop manually ,  how do i do automatically?

Regards
Thomos

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.

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

Ok Dvz,
i under stood, but  every moth how do i will drop that sequence and create again.

it should be automatically.

Regards
Thomos

dvz,

That is directly from the Tom Kyte article I posted.

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.

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;

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.

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