RE: pl/sql help

From: Kenneth Naim <>
Date: Thu, 30 Jul 2009 23:36:02 -0400
Message-ID: <011801ca1190$080dded0$18299c70$_at_com>

Good Point. I would still disable/rebuild indexes.  


From: [] On Behalf Of Jack van Zanen
Sent: Thursday, July 30, 2009 8:01 PM
Cc:; Oracle-L Group Subject: Re: pl/sql help  

as a variant on step 3  

create table with records you want to keep (ctas)

truncate the original

insert append the records back to original  

Minimal undo as well and no need to re-create triggers, indexes etc etc  

downside => downtime  


2009/7/31 Kenneth Naim <>

There are a few issues with your program.

  1. the in list variable cannot be list of values, it should be one number and only a one number. You can multiple variables (up to 1000), for all clause, or dynamic sql but all these methods are over kill development wise and are slower and use more undo over all so I won't go into them.
  2. The developer had the right idea as a single statement will be faster than a loop but will use a lot more undo space. I would size the undo appropriate to the statement and if you want to reclaim the space later just create a new undo tablespace and swap them, and drop the big one.
  3. Depending on how many rows we are talking about, it will most likely be much faster to create a new table as select the rows you want to keep, then add the appropriate constraints, triggers, indexes, statistics etc. on it.
  4. If you don't want to go through step recommendation 3 then you should atleast drop the indexes on the table, then do the delete, then create the indexes. It will use 80+% less than undo and will be much faster; especially if you build the indexes in parallel/ nologging but remember to alter them logging noparallel (or whatever their parallel/logging state was from before dropping them).


-----Original Message-----

From: [] On Behalf Of Sweetser, Joe
Sent: Thursday, July 30, 2009 6:48 PM
To: Oracle-L Group
Subject: pl/sql help

Greetings gurus,
RH Linux AS4

I am trying to write a pl/sql program to delete a bunch of records. This came up because a developer wrote a sql statement to delete everything but it was running out of undo space. I thought it would be easy to throw into a pl/sql program and commit along the way to avoid that issue.

The gist of it is that I am building a list of values to use in the delete statement and passing it in as a bind variable but am getting an error when executing it. The SQL statement is "delete from table where column in (num1, num2, num3, num4,...num50);" I am using a varchar to build the num1, num2, num3... list and then trying to pass the whole thing to sql statement on the execute immediate. When I do, I get an invalid number error. I think I am missing something obvious but can't figure it out.

Here's the output from when I execute it. I added the echo'ing of the sql statement and the list_of_values while trying to debug. I have tried putting the ()'s in the list_of_values but get the same error.

SQL> exec epicenter_delete.whack_it_all
delete from pol_predetail where ParentObjID in (:1)


BEGIN epicenter_delete.whack_it_all; END;

ERROR at line 1:

ORA-01722: invalid number
ORA-06512: at "JOE.EPICENTER_DELETE", line 194
ORA-06512: at line 1

Elapsed: 00:00:00.92
SQL> The program goes something like this:

PROCEDURE whack_it_all IS

  SQL_Stmt     VARCHAR2(512);   -- holds sql stmt to executed
  list_of_values     VARCHAR2(512);   -- holds id's to be deleted


  SELECT ParentObjID
  FROM blah, blah, blah;


  BEGIN   /* Initialize counters/variables */
  tmp2_count := 0;
  list_of_values := '';

  FOR premium_detail_rec IN c1
    list_of_values := list_of_values || premium_detail_rec.ParentObjID;     tmp2_count := tmp2_count+1;
    IF tmp2_count = 50
        SQL_Stmt := 'delete from pol_predetail where ParentObjID in (:1)';

        dbms_output.put_line (SQL_Stmt);
        dbms_output.put_line (list_of_values);
        EXECUTE IMMEDIATE SQL_Stmt using list_of_values;  <----- this
is line 194
        list_of_values := '';
        tmp2_count := 0;
       list_of_values := list_of_values || ',';
    END IF;
  END LOOP; Any/all ideas/help appreciated.





Jack van Zanen

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

-- Received on Thu Jul 30 2009 - 22:36:02 CDT

Original text of this message