7 Replies Latest reply: Jul 7, 2014 11:26 AM by rp0428 RSS

    Delete Data from multiple tables using multiple cursors

    854796

      Hi All,

      we have a database where temporary tables(around 200-300 tables) will be updated by the load process, I am trying to write a procedure to delete the data from all the TMP tables, if the data is older than the value which is defined in the parameter table.

       

      I am not able to find why the procedure is failing when I am trying to compile it. Can any one have a look and please let me know.

       

      This is my first procedure, request your kind inputs for this.

       

       

      CREATE OR REPLACE PROCEDURE DELETE_DATA  IS

         v_table_name varchar2(255);

         v_time_value INTEGER;

        

         CURSOR get_tables IS

           SELECT DISTINCT tbl.table_name

           FROM all_tables tbl

           WHERE tbl.table_name LIKE '%TMP';

          

         CURSOR get_time_value IS

           SELECT time_value from param;

          

         BEGIN

        

         OPEN get_tables;

         LOOP

            FETCH get_tables INTO  v_table_name;

           

            OPEN get_time_value;

            LOOP

               FETCH get_time_value INTO v_data_retention_for;

       

       

      sqlstatement = 'DELETE FROM ' || v_tablename || ' WHERE MONTHS_BETWEEN(SYSDATE, ' ||

                                   UPDATE_DATE || ' ) > ' || v_time_value || ;

       

        LOOP

                     EXECUTE IMMEDIATE sqlstatement;

                     EXIT WHEN SQL%ROWCOUNT = 0;

                     COMMIT;

       

       

            END LOOP;

           

            CLOSE get_time_value;

           

         END LOOP;

        

         CLOSE get_tables;

        

      end DELETE_DATA;

       

       

       

       

       

      Regards,

      Deepti

        • 1. Re: Delete Data from multiple tables using multiple cursors
          Hoek

          v_data_retention_for is not declared nor used...

          • 2. Re: Delete Data from multiple tables using multiple cursors
            padders

            How many LOOP statements? How many END LOOP statements?

            • 3. Re: Delete Data from multiple tables using multiple cursors
              Roger

              - assignment of sqlstatement is wrong should be ":=" instead of "="

              - UPDATE_DATE is a colulmn of your table so no need to concatenate it separately (and if, then like this 'UPDATE_DATE')

              - concatenation of the ";" at the end of sqlstatement is not needed - just terminate your line with an ;

              - exit in the delete loop is weird ... you don't trust oracle?

              - no need to read get_time_value for every table

              - no need to execute delete within a loop

               

              suggestion (even if i would not do it this way - at least i would restrict the tables to those of the schema):

               

              CREATE OR REPLACE PROCEDURE DELETE_DATA
              IS
                 v_table_name all_tables.table_name%TYPE;
                 v_time_value param.time_value%TYPE;
                
                 CURSOR get_tables
                     IS SELECT DISTINCT tbl.table_name FROM all_tables tbl WHERE tbl.table_name LIKE '%TMP';

                 CURSOR get_time_value
                     IS SELECT time_value FROM param;
              BEGIN
                 OPEN  get_time_value;
                 fETCH get_time_value INTO v_time_value;
                 CLOSE get_time_value;
                
                 OPEN  get_tables;
                 LOOP
                    FETCH get_tables INTO v_table_name;
                    exit WHEN get_tables%NOTFOUND;
                   
                    sqlstatement := 'DELETE FROM ' || v_tablename || ' WHERE MONTHS_BETWEEN(SYSDATE, UPDATE_DATE ) > ' || v_time_value;
                    EXECUTE IMMEDIATE sqlstatement;

                 END LOOP;

                 CLOSE get_tables;

               

                 COMMIT; -- ? you cannot do this outside?
              END DELETE_DATA;
              /

              • 4. Re: Delete Data from multiple tables using multiple cursors
                854796

                HI Roger,

                 

                Thanks for your quick reply.

                 

                As this is my first PL/SQL code, I thought this is the way of doing it. Please suggest a approach if this is not a good way of handling this scenario.

                 

                I have updated the code little bit.

                 

                 

                 

                CREATE OR REPLACE PROCEDURE DELETE_DATA

                IS

                   v_table_name all_tables.table_name%TYPE;

                   v_time_value param.time_value%TYPE;

                   sqlstatement VARCHAR2(255);

                  

                   CURSOR get_tables

                       IS SELECT DISTINCT tbl.table_name FROM all_tables tbl WHERE tbl.table_name LIKE '%TMP';

                   CURSOR get_time_value

                       IS SELECT time_value FROM param;

                BEGIN

                   OPEN  get_time_value;

                   fETCH get_time_value INTO v_time_value;

                   CLOSE get_time_value;

                  

                   OPEN  get_tables;

                   LOOP

                      FETCH get_tables INTO v_table_name;

                      exit WHEN get_tables%NOTFOUND;

                     

                      sqlstatement := 'DELETE FROM ' || v_table_name || ' WHERE MONTHS_BETWEEN(SYSDATE, UPDATE_DATE ) > ' || v_time_value;

                      EXECUTE IMMEDIATE sqlstatement;

                   END LOOP;

                   CLOSE get_tables;

                 

                   COMMIT;

                END DELETE_DATA;

                /

                 

                The code was compiled, but when I am trying to execute I am getting the below error

                 

                 

                 

                Error starting at line 1 in command:

                EXEC PROC DELETE_DATA

                Error report:

                ORA-06550: line 1, column 12:

                PLS-00103: Encountered the symbol "DELETE_DATA" when expecting one of the following:

                 

                 

                   := . ( @ % ;

                The symbol ":=" was substituted for "DELETE_DATA" to continue.

                06550. 00000 -  "line %s, column %s:\n%s"

                *Cause:    Usually a PL/SQL compilation error.

                *Action:

                 

                can you please check where I am doing it wrong.

                 

                Regards,

                Deepti

                • 5. Re: Delete Data from multiple tables using multiple cursors
                  Hoek

                  Use:

                   

                  SQL> exec delete_data

                  • 6. Re: Delete Data from multiple tables using multiple cursors
                    BluShadow

                    EXEC is just an SQL*Plus wrapper which internally get's expanded to BEGIN ... END; around the statement you supply.

                     

                    So do you think the PL code...

                     

                    BEGIN

                      PROC DELETE_DATA;

                    END;

                     

                    is a valid way to call a procedure in PL/SQL?

                     

                    or would

                     

                    BEGIN

                      DELETE_DATA;

                    END;

                    perhaps be better? 

                     

                    In which case, as Hoek shows, you just want...

                     

                    EXEC DELETE_DATA;

                    • 7. Re: Delete Data from multiple tables using multiple cursors
                      rp0428
                      can you please check where I am doing it wrong.

                      Just about everything you are doing is wrong.

                         CURSOR get_tables

                             IS SELECT DISTINCT tbl.table_name FROM all_tables tbl WHERE tbl.table_name LIKE '%TMP';

                         CURSOR get_time_value

                             IS SELECT time_value FROM param;

                      The first cursor doesn't take the SCHEMA/OWNER into account so will return tables for ALL schemas in the DB. The table names will be returned in some random order. That assumes that none of the tables have any dependency on other tables. You can NOT delete from the SCOTT.DEPT table if there are child rows in the SCOTT.EMP table.

                       

                      That cursor also assumes that all tables that end in 'TMP' should have their data deleted. That is a DANGEROUS assumption.

                       

                      The second cursor assumes there is only ONE row in the 'param' table. What if there are two or more rows?

                       

                      You are using ONE procedure to do more than one thing: 1) get a list of tables, 2) get a parameter value, 3) delete data

                       

                      You are NOT using ANY exception handling to deal with any of those three functional areas

                       

                      If data is periodically loaded and deleted based on a date why aren't the temp tables partitioned? Then you could just drop the unneeded partitions and be done with it.

                       

                      You are also NOT following one of the most important best practices for writing dynamic code: never try to automate what you can NOT do manually. Use DBMS_OUTPUT.PUT_LINE to print out the SQL on a sql*plus console. Then execute that SQL manually to make sure it works properly.

                       

                      You are NOT using a log table to log the results of each step in the processs and the deletion of each table. So when the process ends how will you know if it is successful?

                       

                      There should be multiple procedures for the process. One procedure is the CONTROL procedure; the others do the actual work:

                      sp_delete_all_temp_data

                         a. sp_get_the_parameters

                         b. sp_get_the_table_list

                         c. sp_delete_data_from_one_table

                      The procedure that does all of the work should delete/truncate data from ONE table. It takes parameters that tell it what table to use, whether to delete or truncate the data and what criteria to use (date?) for the action.

                       

                      The control procedure calls that one 'work' procedure in a loop and passes it one table name each time. The control procedure also performs appropriate logging and exception handling.

                       

                      Write modular code. Each module does ONE thing.