12 Replies Latest reply: Oct 28, 2005 10:53 AM by JustinCave RSS

    Once a month Drop/Recreate Sequence

    458520
      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
          JustinCave
          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
            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
              JustinCave
              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
                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
                  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
                    JustinCave
                    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
                      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
                        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
                          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
                            JustinCave
                            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
                              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
                                JustinCave
                                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