CODEWITHSUNDEEP
oracle-databaseplsql
Caverman
Caverman

Reputation: 3707

Oracle parameterized SQL using IN clause with multiple values not working

I have an parameterized SQL statement that uses the IN clause in order to update multiple records with one query. It is an integer field, the RID (record ID), to do the update. If I pass only one RID it works but if I pass multiple values I get Error: ORA-01722: invalid number.

This is the code:

sbQuery.Append("update EXC_LOG set supv_emp_id=:userId, status=:exceptionStatus, supv_comments=:exceptionComment ");
sbQuery.Append("where RID in (:rid)");
ctx.Database.ExecuteSqlCommand(sbQuery.ToString(),
                                new OracleParameter("userId", UserId),
                                new OracleParameter("exceptionStatus", exceptionStatus),
                                new OracleParameter("exceptionComment", comment),
                                new OracleParameter("rid", rid));

If I pass in one value RID it works but if I pass in multiples comma separated (ex: 1234,5566,8899) I get the Invalid Number error.

How can I pass in multiple integer values when using parameters?

Upvotes: 0

Views: 777

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

You are passing a single string argument in to IN(). If that happens to contain a single number then you're effectively doing:

where RID in ('12345')

which is processed with an implicit conversion, since your RID column is numeric, as:

where RID in (to_number('12345'))

which is fine. But with multiple values in a single string argument you're really trying to do:

where RID in (to_number('12345,5566,8899'))

and to_number('12345,5566,8899') will throw ORA-01722: invalid number.

There are various ways to unpack a delimited string into individual values but a simple one is by treating them as an XPath sequence and putting them through an XMLTable call:

sbQuery.Append("where RID in (select RID from XMLTable(:rid columns RID number path '.'))");

As a demo of that approach, first how the XMLTable call expands the string using a SQL*Plus bind variable:

var rid varchar2(30);

exec :rd := '12345,5566,8899';

select RID from XMLTable('12345,5566,8899' columns RID number path '.');

       RID
----------
     12345
      5566
      8899

and then in a dummy query against a dummy table:

with EXC_LOG (RID, SUPV_EMP_ID, STATUS, SUPV_COMMENT) as (
            select 12345, 123, 'OK', 'Blah blah' from dual
  union all select 8899, 234, 'Failed', 'Some comment' from dual
  union all select 99999, 456, 'Active', 'Workign on it' from dual
)
select *
from EXC_LOG
where RID in (select RID from XMLTable('12345,5566,8899' columns RID number path '.'));

       RID SUPV_EMP_ID STATUS SUPV_COMMENT 
---------- ----------- ------ -------------
     12345         123 OK     Blah blah    
      8899         234 Failed Some comment

Your code will just do an update instead of a select, using the same filter.

Upvotes: 3

Related Questions