7 Replies Latest reply: Jan 11, 2013 9:05 AM by Keith Jamieson RSS

    creating procedure

    Vili Dialis
      Hello,
      I am on 11gr2
      and I am trying to creata a procedure for droping partitions.
      This is what I have done until now but I am getting errors so I dont' know where i went wrong.
      I am trying to drop the last partition in the partitioned table.
      create or replace procedure drop_part
      as
         counter     NUMBER;
         part_name   VARCHAR (20);
      BEGIN
         SELECT partition_name
           INTO part_name
           FROM user_tab_partitions
          WHERE table_name = 'TABLE_A' AND partition_position = 1;
      
         SELECT COUNT (*)
           INTO counter
           FROM user_tab_partitions
          WHERE table_name = 'TABLE_A';
      
         IF counter > 9
         THEN
              dbms_output.put_line(
            execute immediate 'alter table TABLE A drop partition' || part_name|| ';');
            end if;
      end;
      and this ends up with
      SQL> sho errors procedure drop_part
      Errors for PROCEDURE DROP_PART:
      
      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      19/15    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one
               of the following:
               . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
               <an exponent (**)> <> or != or ~= >= <= <> and or like like2
               like4 likec as between from using || multiset member
               submultiset
               The symbol "( was inserted before "IMMEDIATE" to continue.
      
      19/81    PLS-00103: Encountered the symbol ";" when expecting one of the
               following:
               . ( ) , * % & = - + < / > at in is mod remainder not rem =>
      
      LINE/COL ERROR
      -------- -----------------------------------------------------------------
               <an exponent (**)> <> or != or ~= >= <= <> and or like like2
               like4 likec as between from using || multiset member
               submultiset
               The symbol ")" was substituted for ";" to continue.
      can you suggest how can I correct this ?
        • 1. Re: creating procedure
          BluShadow
          Vili Dialis wrote:
          dbms_output.put_line(
          execute immediate 'alter table TABLE A drop partition' || part_name|| ';');
          end if;
          end;
          dbms_output.put_line requires a VARCHAR2 parameter.

          If you want to know what SQL statement is going to be executed by your execute immediate, you would be better assigning the SQL to a variable and using dbms_output to output the variable...
            v_sql := 'alter table TABLE A drop partition '||part_name;
            dbms_output.put_line(v_sql);
            -- execute immediate v_sql;
          Note: SQL statements executed using execute immediate should NOT have a ";" on the end of the statement. That's a terminator used by SQL*Plus or if the code is statically executed within PL/SQL.

          You were also missing a space after the word "partition" in your string.
          • 2. Re: creating procedure
            avish16
            Hi i just went through your code and found the following problematic -

            --
            THEN
            dbms_output.put_line(
            execute immediate 'alter table TABLE A drop partition' || part_name|| ';');
            end if;
            --

            Not sure you want to execute the statement from procedure or just want the dbms_output. You cant have any string without enclosing quotes in dbms_output just like taht, just modify accordingly and try.
            • 3. Re: creating procedure
              Keith Jamieson
              IF counter > 9
                 THEN
                      dbms_output.put_line(
              'execute immediate'|| 'alter table TABLE A drop partition' || part_name|| ';');
                    end if;
              end;
              Should fix your error which is as a result of mis using dbms_output.put_line.

              You should put your string in a variable and then just display the variable. It is a lot easier to find mistakes this way.

              When you say you are trying to drop the last partition, do you mean the last remaining partition, or do you mean the last partition created.


              If its the last remaining partition, you cannot drop it. You have to drop the table instead.
              • 4. Re: creating procedure
                Vili Dialis
                I am dropping always the oldest partition, I guess using a partition_postition column with the value of 1 will always give me the odlest partition , correct ?
                Can someone please suggest also some error handelr for this,
                so if something went wrong that ora error can be written in some table I can create only for that error purposes... ?
                Should I use WHEN OTHERS THEN clause... ?
                • 5. Re: creating procedure
                  Keith Jamieson
                  Vili Dialis wrote:
                  I am dropping always the oldest partition, I guess using a partition_postition column with the value of 1 will always give me the odlest partition , correct ?
                  Why not test your assumption.

                  Create a table with 4 partitions. Drop the oldest partition and then see if the partition number gets reordered.

                  That way you will know for definite.

                  I suspect that oracle will not reorder partition numbers, so if you create 4 partitions and drop the first partion , you will be left with partition numbers 2, 3, and 4, rather than 1, 2, and 3 as you expect.

                  I
                  Can someone please suggest also some error handler for this,
                  so if something went wrong that ora error can be written in some table I can create only for that error purposes... ?
                  Should I use WHEN OTHERS THEN clause... ?
                  You should not use when others. I have shown below how to define your own exception.
                  You should probably use raise_application_error to put in an aprpopriate error message.
                  There is a section in the FAQ (Link at top of this forum) for exception handling.




                  create or replace procedure drop_part
                  as
                     counter     NUMBER;
                     PART_NAME   VARCHAR (20);
                     PARTITION_DOES_NOT_EXIST EXCEPTION;
                     pragma exception_init( PARTITION_DOES_NOT_EXIST, -20001);
                  BEGIN
                     begin
                     SELECT partition_name
                       INTO part_name
                       FROM user_tab_partitions
                      WHERE TABLE_NAME = 'TABLE_A' AND PARTITION_POSITION = 1;
                    EXCEPTION
                    WHEN NO_DATA_FOUND
                    THEN
                        NULL;
                     end;
                     SELECT COUNT (*)
                       INTO counter
                       FROM user_tab_partitions
                      WHERE table_name = 'TABLE_A';
                   
                     IF COUNTER = 0
                     THEN
                         raise PARTITION_DOES_NOT_EXIST;
                     ELSIF counter > 9
                     THEN
                          DBMS_OUTPUT.PUT_LINE(
                        'execute immediate '|| 'alter table TABLE A drop partition' || part_name);
                        END IF;
                  end;
                  • 6. Re: creating procedure
                    Vili Dialis
                    Thank you Keith ,
                    this is where I stopped now,
                    I didnt read your message fully so I'l just wrote where am I
                    CREATE OR REPLACE PROCEDURE drop_part
                    AS
                       counter     NUMBER;
                       part_name   VARCHAR (20);
                       err_code    NUMBER;
                       err_msg     VARCHAR2 (64);
                       v_sql       VARCHAR2(200);
                    BEGIN
                       SELECT partition_name
                         INTO part_name
                         FROM user_tab_partitions
                        WHERE table_name = 'TABLE_A' AND partition_position = 1;
                    
                       SELECT COUNT (*)
                         INTO counter
                         FROM user_tab_partitions
                        WHERE table_name = 'TABLE_A';
                    
                       IF counter > 9
                       THEN
                          v_sql:= (
                             'alter table table_a drop partition ' || part_name || ';');
                             execute immediate v_sql;
                       END IF;
                    EXCEPTION
                       WHEN OTHERS
                       THEN
                          err_code := SQLCODE;
                          err_msg := SUBSTR (SQLERRM, 1, 200);
                    
                          INSERT INTO part_drop_err (error_number, error_message)
                               VALUES (err_code, err_msg);
                    END;
                    and this is in valid state,
                    but when i execute it, it says sucesfully completed but partition is not droped.
                    I cant figure out where i went wrong here.
                    • 7. Re: creating procedure
                      Keith Jamieson
                      When it says Successfully Completed, that means that the procedure completed successfully. If it threw an error the error was caught and suppressed.

                      This is a problem with your exception handler.

                      I am assuming you looked at your error table using another session and it was empty.

                      However there was no commit in your exception handler.

                      The proper way to log errors to a table is to use a procedure which is defined using the autonomous_transaction pragma.

                      This means that you can issue a commit in the logging procedure without affecting the
                      main transaction.

                      For the moment , I would just remove your exception handler and let the procedure throw the error.

                      You obviously missed the point made by blueshadow saying that you do not terminate the sql_string with a semi-colon when you use execute immediate;