RE: pl/sql help

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Thu, 30 Jul 2009 19:23:51 -0400
Message-ID: <00f301ca116c$cdb8f030$692ad090$_at_com>

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).

Ken

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sweetser, Joe
Sent: Thursday, July 30, 2009 6:48 PM
To: Oracle-L Group
Subject: pl/sql help

Greetings gurus,

10.2.0.2
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)

10110,10111,10112,10113,10114,10115,10116,10117,10118,10119,10120,10121,
10122,10123,10124,10125,10126,10145,10146,10147,10148,10149,10150,10151,
10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163,
10164,10165,10166,10167,10168,10169,10170,10171,10190,10191,10192,10193,
10194,10195

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

<snip>

   CURSOR c1 IS
   SELECT ParentObjID
   FROM blah, blah, blah;

<snip>

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

   FOR premium_detail_rec IN c1
   LOOP

     list_of_values := list_of_values || premium_detail_rec.ParentObjID;
     tmp2_count := tmp2_count+1;
     IF tmp2_count = 50
     THEN
         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;
     ELSE
        list_of_values := list_of_values || ',';
     END IF;

   END LOOP; Any/all ideas/help appreciated.

-joe

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 30 2009 - 18:23:51 CDT