RE: pl/sql help

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

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




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

Other related posts: