pl/sql help

From: Sweetser, Joe <>
Date: Thu, 30 Jul 2009 16:48:24 -0600
Message-ID: <89FD2C8D7D551D4598EF20C7E42FEB4504280A16_at_earthquake.ICAT.COM>

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


Received on Thu Jul 30 2009 - 17:48:24 CDT

Original text of this message