12 Replies Latest reply: Dec 13, 2012 1:48 PM by choti RSS

    dynamic sql

    choti
      Inside a procedure can i dop and create sequence using dynamic sql. currently it is giving sequence doesnot exist with the following command
      procedure p1 is
      Begin
      EXECUTE IMMEDIATE 'DROP SEQUENCE customer_s';
        EXECUTE IMMEDIATE 'CREATE SEQUENCE customer_s  MINVALUE 1   START WITH 1   INCREMENT BY 1   CACHE 20';
      
      End;
        • 1. Re: dynamic sql
          JustinCave
          Can you? Sure.
          Should you? Almost certainly not.

          If you're getting an error when you try to drop the sequence stating that the sequence does not exist, the obvious question is whether you are absolutely certain that the sequence actually exists? It seems likely that it doesn't in which case the DROP command fails, you don't catch the exception, so the CREATE statement is never executed.

          Taking a step back, though, why would you want to drop and re-create a sequence? That almost always implies that you are trying to use the sequence for something more than a unique identifier which is almost always incorrect.

          Justin
          • 2. Re: dynamic sql
            choti
            i would like to reset sequence and start again
            • 3. Re: dynamic sql
              rp0428
              >
              Inside a procedure can i dop and create sequence using dynamic sql. currently it is giving sequence doesnot exist with the following command

              procedure p1 is
              Begin
              EXECUTE IMMEDIATE 'DROP SEQUENCE customer_s';
                EXECUTE IMMEDIATE 'CREATE SEQUENCE customer_s  MINVALUE 1   START WITH 1   INCREMENT BY 1   CACHE 20';
               
              End;
              Well since the first thing you do is the DROP if the sequence doesn't exist your code will raise an exception and never do the CREATE.

              You need to put the DROP into its own block so you can trap the exception if it doesn't exist and ignore it. Pseudo-code
              Begin
                BEGIN
                    EXECUTE IMMEDIATE 'DROP SEQUENCE customer_s';
                EXCEPTION 
                   WHEN -doesn't exist exception THEN
                        NULL;
                END;
                BEGIN
                    EXECUTE IMMEDIATE 'CREATE SEQUENCE customer_s  MINVALUE 1   START WITH 1   INCREMENT BY 1   CACHE 20';
                END;
              End;
              • 4. Re: dynamic sql
                JustinCave
                choti wrote:
                i would like to reset sequence and start again
                Why? What is the business reason that you would want to reset the sequence?

                Again, if you are using a sequence for anything other than providing a unique identifier, you're almost certainly doing something wrong. Wanting to restart the sequence implies that you are trying to use the sequence as something other than a unique identifier generator.

                Justin
                • 5. Re: dynamic sql
                  Solomon Yakobson
                  choti wrote:
                  i would like to reset sequence and start again
                  Just arbitrarily or when sequence reaches a certain value? I am certain changing it arbitraily is not a business requirement. And if you want to reset sequence when it reaches certain value, use CYCLE. For example, sequence below generates values between 1 and 3:
                  SQL> create sequence OneTwoThree
                    2  minvalue 1
                    3  maxvalue 3
                    4  start with 1
                    5  cycle
                    6  nocache
                    7  /
                  
                  Sequence created.
                  
                  SQL> select  OneTwoThree.nextval
                    2    from  emp
                    3  /
                  
                     NEXTVAL
                  ----------
                           1
                           2
                           3
                           1
                           2
                           3
                           1
                           2
                           3
                           1
                           2
                  
                     NEXTVAL
                  ----------
                           3
                           1
                           2
                  
                  14 rows selected.
                  
                  SQL> 
                  SY.
                  • 6. Re: dynamic sql
                    Frank Kulash
                    Hi,
                    choti wrote:
                    i would like to reset sequence and start again
                    Why?
                    Changing the value of a sequence after it has been created is not a normal thing to do. As others have suggested, you may have a flawed data model, or a poor grasp of business requirements.

                    If you really must change the value, and you're sure nobody else is getting new numbers while you're resetting the sequence, then you can give it a new value like this:
                    CREATE OR REPLACE PROCEDURE  reset_seq
                    (   in_seq_name       VARCHAR2
                    ,   in_new_val       PLS_INTEGER
                    )
                    IS
                        sql_txt     VARCHAR2 (1000);     -- Dynamic SQL statement 
                        temp_inc     PLS_INTEGER;          -- Temporary increment
                    BEGIN
                        sql_txt := 'SELECT ' || in_seq_name
                                               || '.NEXTVAL FROM dual';
                        dbms_output.put_line (sql_txt || '<= to get current value');     -- For debugging 
                        EXECUTE IMMEDIATE sql_txt INTO temp_inc;
                        temp_inc := (in_new_val - temp_inc) - 1;
                    
                        sql_txt := 'ALTER SEQUENCE ' || in_seq_name
                                                                 || ' INCREMENT BY '
                                         || temp_inc;
                        dbms_output.put_line (sql_txt || '<= before resetting');          -- For debugging 
                        EXECUTE IMMEDIATE sql_txt;
                    
                        sql_txt := 'SELECT ' || in_seq_name
                                               || '.NEXTVAL FROM dual';
                        dbms_output.put_line (sql_txt || '<= to actually reset');          -- For debugging 
                        EXECUTE IMMEDIATE sql_txt INTO temp_inc;
                    
                        sql_txt := 'ALTER SEQUENCE ' || in_seq_name
                                                                 || ' INCREMENT BY 1';     -- Assumuing  1 is correct
                        dbms_output.put_line (sql_txt || '<= after resetting');          -- For debugging 
                        EXECUTE IMMEDIATE sql_txt;
                    END reset_seq;
                    /
                    Since this does not drop the sequence, you will not need to grant privileges on it again, and objects that depend on the sequence will not need to be re-compiled.
                    • 7. Re: dynamic sql
                      choti
                      working on datawarehouse for fullload i want to reset and do it again
                      • 8. Re: dynamic sql
                        choti
                        I am sure on the requirement. I want to drop sequence and recreate with start value 1 and increment by 1. I would like to have in procedure.
                        • 9. Re: dynamic sql
                          Frank Kulash
                          Hi,
                          choti wrote:
                          I am sure on the requirement. I want to drop sequence and recreate with start value 1 and increment by 1.
                          That's not a good reason. Who cares what the exact numbers are? What does it matter if you use 9876 or 1 as an id number?
                          What exactly is the business requirement you're trying to meet?
                          I would like to have in procedure.
                          Isn't that exactly what I posted?

                          Edited by: Frank Kulash on Dec 13, 2012 2:38 PM
                          • 10. Re: dynamic sql
                            Solomon Yakobson
                            choti wrote:
                            working on datawarehouse for fullload i want to reset and do it again
                            So why do you need dynamic SQL for that? Why do you need to do it inside procedure? It is one time deal. So do it manually from SQL. And don't forget to recompile sequence dependent objects - the will be invalidated when you drop sequence.

                            SY.

                            Edited by: Solomon Yakobson on Dec 13, 2012 2:39 PM
                            • 11. Re: dynamic sql
                              choti
                              we doing testing so in the stage so requirement is start with 1
                              • 12. Re: dynamic sql
                                JustinCave
                                That is not a sensible requirement.

                                A sequence generates unique identifiers. It shouldn't matter whether a row has a sequence-generated key of 1 or 57 or 863. It makes no sense to require that a row with an id value of 1 exists let alone to try to test what that data represents. If you have tests that require that a particular key exists, that implies that the key itself is meaningful and, therefore, that it is not appropriate to use a sequence to generate it.

                                Justin