Joe, You can get it to work if you slightly modify your statement. If you don't use the 'using' clause it works fine: declare in_str varchar2(10) := '1,2,3'; sqlstr varchar2(100) := 'select 1 from dual where 1 in (' || in_str || ')'; begin execute immediate sqlstr; end; Tom -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l