This discussion is archived
12 Replies Latest reply: Oct 28, 2005 8:53 AM by Justin Cave RSS

Once a month Drop/Recreate Sequence

458520 Newbie
Currently Being Moderated
Hello,

I have very little experience with a stored procedure, but I'm working on learning. This is what I am trying to achieve:

I have a sequence that I need reset once at the beginning of each month. I am using it as a monthly counter. I can code in my program to drop the sequence and re-create it once a month, but I'm really looking to do this with some type of triggered stored procedure within the DB.

Thanks for all suggestions,
Rick
  • 1. Re: Once a month Drop/Recreate Sequence
    Justin Cave Oracle ACE
    Currently Being Moderated
    While it is certainly possible to use DBMS_JOB to schedule this sort of thing, I would strongly suggest looking for alternatives. Dropping & recreating a sequence is almost always an indication that your design has some issues.

    Sequence-generated values should not encode any information. Sequences can skip values, for example. On a RAC system, they can come out of order as well (unless you've specifically added the ORDER clause to the CREATE SEQUENCE statement).

    Why do you need a counter that restarts every month? What is the business problem you're attempting to solve?

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 2. Re: Once a month Drop/Recreate Sequence
    458520 Newbie
    Currently Being Moderated
    Thanks for the heads up on some possible design issues. This is the general idea of what I'm trying to accomplish:

    I have some data running through my site in the form of user uploads and I use a sequence to assign a number to each "item" that runs though. Every item that is upload gets the next sequence number assigned to it.

    I intend on using this as a monthly counter, hence my reason for dropping and resetting the sequence once monthly.

    Thanks!
  • 3. Re: Once a month Drop/Recreate Sequence
    Justin Cave Oracle ACE
    Currently Being Moderated
    What does the monthly counter give you from a business standpoint?

    - Do you want to tell people-- you uploaded 30 files in January, 40 in February, 25 in March, etc?
    - Do you want to give people a report that says "the first file you uploaded this month was a.txt, the second file was b.jpg, etc"?
    - Do you want to give people a unique identifier so they can call up support and ask about upload 654903?

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 4. Re: Once a month Drop/Recreate Sequence
    458520 Newbie
    Currently Being Moderated
    It will serve as both situation 1 and 3 from your bullet points.

    It will end up being a unique identifier that is reset every month. Upload 1010001 would represent and upload done on Jan 1 and it is the first one of the month.

    201001 would represent upload 1 in Feb. 2010002 would be uplod 2 in Feb. and so on...

    I hope that helps to clarify

    Thanks
  • 5. Re: Once a month Drop/Recreate Sequence
    6363 Guru
    Currently Being Moderated
    You can't use a sequence as a counter, they are guaranteed only to be unique and will have gaps.

    You don't want to store a counter in the database, it will mean all inserts can only happen one at a time, else the count will be wrong, and no deletes can ever take place.

    You would write a query using count(*) to retrieve the appropriate counts you require.
  • 6. Re: Once a month Drop/Recreate Sequence
    Justin Cave Oracle ACE
    Currently Being Moderated
    For a counter (bullet #1), 3360 is right that you want to use an appropriate COUNT(*).

    For a unique identifier (bullet #3), you generally don't want to reset the sequence. You may want to start the counter at an appropriate minimum (i.e. 10,000 if you want 5-digit identifiers), but it shouldn't matter that upload 31204 was the 33rd upload in January and 33205 was the 51st upload in March. If you want to track information about when an upload occurred, that ought to be stored in a separate column. Sequence-generated id's ought to impart no information-- you want them to be a unique identifier and nothing more.

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 7. Re: Once a month Drop/Recreate Sequence
    245482 Newbie
    Currently Being Moderated
    I.e. you want to represent this information in multiple columns rather than trying to pack it into one (overly clever) column. So one column would contain a sequence-generated value that you can use to uniquely identify a row, another column would contain the date/time at which the data was loaded.
  • 8. Re: Once a month Drop/Recreate Sequence
    6363 Guru
    Currently Being Moderated
    Here is an example of what Justin and Scott are saying using a view and analytic functions to give you all the information you are looking for. Note I chose an idiosyncratic increment value in this demo purely to enforce the concept that a sequence generated id should be unique only and not have any meaning.
    SQL> create table upload (
      2      id              number,
      3      description     varchar2(10),
      4      upload_date     date
      5      );

    Table created.

    SQL> create sequence upload_seq start with 10000 increment by 17;

    Sequence created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 1', date '2005-01-07');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 2', date '2005-01-15');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 3', date '2005-01-22');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 4', date '2005-02-02');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 5', date '2005-02-25');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 6', date '2005-03-10');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 7', date '2005-03-16');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 8', date '2005-03-21');

    1 row created.

    SQL> insert into upload values (upload_seq.nextval, 'Upload 9', date '2005-03-23');

    1 row created.

    SQL> create or replace view upload_monthly as
      2  select id, description, upload_date,
      3      row_number() over (
      4          partition by trunc(upload_date,'mm') order by upload_date
      5          ) monthly_count,
      6      count(*) over (
      7          partition by trunc(upload_date,'mm')
      8          ) monthly_total
      9  from upload;

    View created.

    SQL>
    SQL> select * from upload_monthly;

            ID DESCRIPTIO UPLOAD_DA MONTHLY_COUNT MONTHLY_TOTAL
    ---------- ---------- --------- ------------- -------------
         10000 Upload 1   07-JAN-05             1             3
         10017 Upload 2   15-JAN-05             2             3
         10034 Upload 3   22-JAN-05             3             3
         10051 Upload 4   02-FEB-05             1             2
         10068 Upload 5   25-FEB-05             2             2
         10085 Upload 6   10-MAR-05             1             4
         10102 Upload 7   16-MAR-05             2             4
         10119 Upload 8   21-MAR-05             3             4
         10136 Upload 9   23-MAR-05             4             4

    9 rows selected.

    SQL>
    Hope this helps.
  • 9. Re: Once a month Drop/Recreate Sequence
    461255 Newbie
    Currently Being Moderated
    How do you find how if the sequence already exist?

    for example: create sequence upload_seq start with 10000 increment by 17;
    how do you tell upload_seq already exist?

    too bad you cannnot do create or replace sequence upload_seq;
  • 10. Re: Once a month Drop/Recreate Sequence
    Justin Cave Oracle ACE
    Currently Being Moderated
    You can query the USER_SEQUENCES table to see what sequences exist. You can also code an exception handler that would catch the Oracle error from either dropping a non-existant sequence or creating an existing sequence.

    As this thread indicated, however, dropping & re-creating a sequence almost always indicates that you are misusing the sequence. I would hope that Oracle would not make it any easier to do things that ought not be done in the first place.

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
  • 11. Re: Once a month Drop/Recreate Sequence
    461255 Newbie
    Currently Being Moderated
    thank you for your advice, I see what you are saying.

    what I am doing is, if table does not exist, then drop the sequence then recreate it.

    thanx very much.
  • 12. Re: Once a month Drop/Recreate Sequence
    Justin Cave Oracle ACE
    Currently Being Moderated
    I don't quite follow what the existance of a table has to do with the existance of a sequence. What is the relationship?

    Why might the table not exist? Tables ought to be created at install time, they should not be coming & going. You should know what tables exist well in advance...

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC