12 Replies Latest reply: Mar 11, 2010 10:20 AM by 674481 RSS

    Delete Rows from table using pl/sql

    674481
      I am working on deleting rows from this table (my.table_job_status) that are old and with a lot of lines. What are want to accomplish is to select those lines with it job# and have them deleted in seperate file created. The point is I will like it to be dynamically as mush as possible.

      For example, if I do something like this "select * from my_table where num_lines > 50000", I will like for the result to be place in a sql file "my_sql" and executed. Like execute immediate my_sql.

      This is what I am working on:

      cursor my_cursor is
      select table_name, jobid, submit_time, starttime, endtime, status, num_lines from
      my.table_job_status
      where endtime < sysdate - 60 AND num_lines > 1000000;
      Begin
      for del_r in my_cursor loop
      if --the above is true THEN
      my_sql:='delete from' || my.table_job_status
      execute immediate my_sql;
      end if
      end loop
      exeption
      when others then
      if sqlcode != -942 then
      raise;
      end IF;
      end;
      commit;


      As you can see below here, we are currently deleting tables in the same manor, but I don't know how to accomplish this in deleting rows from tables:

      cursor obj_cursor is
      select
      object_name,
      object_type,
      owner
      from
      dba_objects
      where
      owner='MY'
      and object_type in ('TABLE','VIEW')
      and object_name like wk_jobstr ;

      WK_SQL VARCHAR2(500) ;
      WK_JOBSTR VARCHAR2(500) ;
      out_f utl_file.file_type ;
      BEGIN
      wk_jobstr:='JOB_' || ltrim(to_char(wk_job)) || '%';
      for wk_rec in obj_cursor loop
      if wk_rec.object_type='TABLE' then
      wk_sql:='drop table ' || wk_rec.object_name ;
      --EXECUTE IMMEDIATE WK_SQL ;
      IF 1=2 THEN
      dbms_sql.parse(v_cursor, wk_sql, dbms_sql.native) ;
      ret_val:=dbms_sql.execute(v_cursor) ;
      END IF ;
      SP_SHOW_sql(wk_sql,'wk_sql') ;
      dbms_output.put_line(wk_sql) ;
      if print_yn>0 then
      dbms_output.put_line('table ' || wk_rec.object_name || ' deleted') ;
      end if ;
      elsif wk_rec.object_type='VIEW' then
      wk_sql:='drop view ' || wk_rec.object_name ;
      --EXECUTE IMMEDIATE WK_SQL ;
      IF 1=2 THEN
      dbms_sql.parse(v_cursor, wk_sql, dbms_sql.native) ;
      ret_val:=dbms_sql.execute(v_cursor) ;
      END IF ;
      if print_yn>0 then
      dbms_output.put_line('view ' || wk_rec.object_name || ' deleted') ;
      end if ;
      end if ;
      end loop ;
      exception
      when others then
      if sqlcode != -942 then
      raise ;
      end if ;
      end ;
      commit;
        • 1. Re: Delete Rows from table using pl/sql
          Warren Tolentino
          the second part of your posting is not really deleting the rows from a table but droping the table and/or the view. before there was EXECUTE IMMEDIATE the dbms_sql.parse is used for the same purpose for dynamic query or commands. are you trying to convert your first part to use the dbms_sql.parse?
          • 2. Re: Delete Rows from table using pl/sql
            674481
            Warren,

            Thank you for getting back to me. I know the second part is droping tables and not deleting them, but what I want to do is use the same method to delete rows or lines from the table "my.table" dynamically.

            So what I want to do is select all the row that meet the critirial and use the result to delete those rows. For example if I select only name ending in 'zaza' from a particular table, I want the result store and deleted dynamically only base on the "select".

            Edited by: Albert Zaza on Mar 9, 2010 11:05 AM
            • 3. Re: Delete Rows from table using pl/sql
              653230
              When I loaded your sql into my editor to start picking it apart, it immediately threw the following...

              ERROR line 2, col 1, ending_line 2, ending_col 6, Found 'CURSOR', Expecting: ( end of input SELECT -or- BEGIN DECLARE FUNCTION PACKAGE PROCEDURE TYPE -or- << ALTER ANALYZE ASSOCIATE AUDIT Block_Comment CALL COMMENT COMMIT CREATE Dash_Comment DELETE DISASSOCIATE DROP EXPLAIN FLASHBACK FROM GRANT INSERT LOCK MERGE NOAUDIT ORDER PURGE RENAME REVOKE ROLLBACK SAVEPOINT SET TRUNCATE UPDATE WHERE WITH

              and

              ERROR line 18, col 21, ending_line 18, ending_col 22, Found ':=', Expecting: THEN -or- OR -or- AND -or- BETWEEN IN LIKE LIKE2 LIKE4 LIKEC MEMBER SUBMULTISET -or- -or- ! != < <= <> = > >= ^ ^= IS NOT -or- + - || -or- * / MOD REMAINDER -or- ** -or- (+) AT DAY MULTISET YEAR -or- % . [   -or-   (   -or-   @   -or-   string

              I also see some strings that weren't properly ended....


              Here is the same code with the proper punctuation. But it still doesn't fix the errors just yet. Still looking at the rest of the code to analyze the error.

              (corrected punctuaion)

              CURSOR my_cursor is

              SELECT table_name,
              jobid,
              submit_time,
              starttime,
              endtime,
              status,
              num_lines
              FROM my.table_job_status
              WHERE endtime < SYSDATE - 60 AND num_lines > 1000000;

              BEGIN
              FOR del_r IN my_cursor
              LOOP
              IF the above is TRUE THEN
              my_sql :='delete from' || my.table_job_status
              execute immediate my_sql;
              end if;
              end loop;
              exeption
              when others then
              if SQLCODE != -942 then
              raise;
              end IF;
              end;
              COMMIT;
              • 4. Re: Delete Rows from table using pl/sql
                674481
                Willy,
                Thank you for your comment on the example, but what I want is using the second example method to delete or drop tables in order to delete rows from a table (delete from my.table_status).
                • 5. Re: Delete Rows from table using pl/sql
                  TheOtherGuy
                  I'm really confused by the question... regardless that the code below does not look correct.

                  1. Row 8 is my_sql:='delete from' || my.table_job_status are you sure about this? it will loop through the cursor and run exactly same delete statement every time, it makes no sense. Didn't you want to do something like this? my_sql:='delete from' || del_r.table_name

                  2. make sure you use ROLLBACK after you raise error or commit right after EXECUTE IMMEDIATE



                  cursor my_cursor is
                  select table_name, jobid, submit_time, starttime, endtime, status, num_lines from
                  my.table_job_status
                  where endtime < sysdate - 60 AND num_lines > 1000000;
                  Begin
                  for del_r in my_cursor loop
                  if --the above is true THEN
                  my_sql:='delete from' || my.table_job_status
                  execute immediate my_sql;
                  end if
                  end loop
                  exeption
                  when others then
                  if sqlcode != -942 then
                  raise;
                  end IF;
                  end;
                  commit;
                  • 6. Re: Delete Rows from table using pl/sql
                    674481
                    1. Row 8 is my_sql:='delete from' || my.table_job_status are you sure about this? it will loop through the cursor and run exactly same delete statement every time, it makes no sense. Didn't you want to do something like this? my_sql:='delete from' || del_r.table_name
                    Yes you are right and I can change the above to work. My problem is can you or anybody give me an example of code that will delete rows from a particuler table using the format below?


                    >
                    cursor my_cursor is
                    select table_name, jobid, submit_time, starttime, endtime, status, num_lines from
                    my.table_job_status
                    where endtime < sysdate - 60 AND num_lines > 1000000;
                    Begin
                    for del_r in my_cursor loop
                    if --the above is true THEN
                    my_sql:='delete from' || my.table_job_status
                    execute immediate my_sql;
                    end if
                    end loop
                    exeption
                    when others then
                    if sqlcode != -942 then
                    raise;
                    end IF;
                    end;
                    commit;
                    • 7. Re: Delete Rows from table using pl/sql
                      TheOtherGuy
                      do you want to delete records from my.table_job_status table or is the table name changing and comes from my.table_job_status table column table_name ?
                      • 8. Re: Delete Rows from table using pl/sql
                        674481
                        do you want to delete records from my.table_job_status table or is the table name changing and comes from my.table_job_status table column table_name ?
                        Yes I will like to delete records from my.table_job_status.

                        The column table_name in my my.table_job_status is null so most of the user don't include the table name when they create a job for a particular report; so I can't use that option- that could have been easier because I could use dba_objects.

                        These are the three things I want accomplished by the PLSQL:

                        - Use a cursor to generate list of rows that meet the cratirial (for example, the result of select * from my.table_job_status)
                        - Display the rows generated in the form of script. Example (delete from cur_my.table.job_status)

                        - Use Execute immediate to the run the script.

                        My question all along has been is this possible?
                        • 9. Re: Delete Rows from table using pl/sql
                          TheOtherGuy
                          I don't have Oracle instance running here (kinda mobile while i am typing) ... I'm sorry if the code has errors

                          1. you have to take EXECUTE IMMEDIATE out of the loop, use the loop only to display the records and then one delete statement to get rid of them
                          2. your delete statement does not include where clause and thus deletes all records
                          3. I would replace sysdate with static date sysdate changes and thus if you use it 2x then it can display and delete different records)
                          4. why do you need execute immediate? there is no need for it in this case unless you deal with grants you dont have and code would not compile




                          cursor my_cursor is
                          select table_name, jobid, submit_time, starttime, endtime, status, num_lines from
                          my.table_job_status
                          where endtime < mydate - 60 AND num_lines > 1000000;

                          BEGIN

                          -- display all records you are going to delete
                          for del_r in my_cursor loop
                          dbms_output.put_line del_r.table_name || ' ' || del_r.job_id
                          end loop

                          -- delete records
                          delete from my.table_job_status
                          where endtime < mydate - 60 AND num_lines > 1000000;
                          commit;


                          END;
                          • 10. Re: Delete Rows from table using pl/sql
                            674481
                            Jiri,

                            The idea is to get records from this table deleted automatically or dynamically without any human intervention. This table (????_job_status) save information about tables created when users create jobs. In this case every job created by a user is save in a table and this table is save in ???_job_status.

                            >
                            1. you have to take EXECUTE IMMEDIATE out of the loop, use the loop only to display the records and then one delete statement to get rid of them
                            I corrected the above.

                            2. your delete statement does not include where clause and thus deletes all records
                            Got this corrected as well
                            3. I would replace sysdate with static date sysdate changes and thus if you use it 2x then it can display and delete different records)
                            That is the idea is to delete records when they are old and too large. If I used static date, it will only continue to look for the same records. What I want is to delete records two years old from todays date and with a lot of lines or records.
                            4. why do you need execute immediate? there is no need for it in this case unless you deal with grants you dont have and code would not compile
                            I need the execute immediate because like I said earlier, I need it dynamic with no human intervention. Efficiency is the key here.

                            This is what you have justed below:

                            >
                            DECLARE
                            cursor del_cursor is
                            select table_name, jobid, submit_time, starttime, endtime, status, num_lines from
                            ita.tpis32_job_status where endtime < '10-MAY-05' AND num_lines > 1000000;
                            BEGIN
                            -- display all records you are going to delete
                            for del_r in del_cursor loop
                            dbms_output.put_line(del_r.table_name || ' ' ||del_r.jobid);
                            end loop;
                            -- delete records
                            delete from ita.tpis32_job_status where endtime < '10-MAY-05' AND num_lines > 1000000;
                            --commit;
                            END;
                            >

                            Edited by: Albert Zaza on Mar 10, 2010 9:08 AM
                            • 11. Re: Delete Rows from table using pl/sql
                              TheOtherGuy
                              you can easily replace DELETE statement with execute immediate, you can also replace static query in cursor with variable query using weekly typed ref cursor - look at weakly typed section in this document http://www.psoug.org/reference/ref_cursors.html


                              One thing what I don't get is why you create so many tables, why you cannot have one central table for all jobs? You can have simple API on top of it to handle all inserts/delete/... we had one single table in our data warehouse for all jobs (this was 7TB+ system) and we never ever purged this table.

                              if somebody really has to see so many tables, then keep one central table and replace tables with views
                              • 12. Re: Delete Rows from table using pl/sql
                                674481
                                you can easily replace DELETE statement with execute immediate, you can also replace static query in cursor with variable query using weekly typed ref cursor
                                Yes i have already done that and I know it can work, I just want to get the basic for now and continue.

                                >
                                One thing what I don't get is why you create so many tables, why you cannot have one central table for all jobs? You can have simple API on top of it to handle all inserts/delete/... we had one single table in our data warehouse for all jobs (this was 7TB+ system) and we never ever purged this table.
                                For this, I mispoke and here is what I ment: I have jobs that are releted to many tables. What I want is to find all tables in dba_objects that are related to a particular jobid.
                                if somebody really has to see so many tables, then keep one central table and replace tables with views
                                I am sorry about this, they are actually jobs, not tables. The job can be related to one or many tables.