0

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?

Improve this question
4
  • Could this be an issue with , and .? Not sure what kind of framework you are using, but maybe the OracleParameter object parses your input 1234,5566,8899 as a single number and not multiple entities. Depending on system and/or NLS settings that number could be interpreted several ways (as in "American" vs. "European" meaning of , and . in numbers). Seen this stuff lead to similar errors Commented Jul 31, 2018 at 15:26
  • It's being passed as a single string, not as a list of numbers, as far as the SQL parser is concerned. How are you setting the rid value? Maybe you can use a collection type instead of a string (and member of instead of in)? Or use this workaround. Commented Jul 31, 2018 at 15:28
  • The "rid" parameter is coming into the function as a string so that it can be a comma separated value. I'm assuming the OracleParameter is auto determining the value type since it's not being specifically set. I'm guessing that when the OracleParameter sees only one values (ex: 12345) then it sends it as an integer but when it sees multiple values and it will assume a string (ex: 12345, 5566, 8899 looks like a string). However, I will say that when I looked up some info about the IN clause the examples would pass comma separated values of integers. Commented Jul 31, 2018 at 15:41
  • No, it'll always send a string, but Oracle can implicitly convert '12345' into a number - it can't implicitly convert '12345,5566,8899' to a number though, and gets ORA-01722 when it tries. You can (clearly) have a comma-separated list of numbers as multiple arguments to in(), but you can't have a single string that happens to contain comma-separated values, which is what you are doing. Well, not without extra handling. Commented Jul 31, 2018 at 15:45

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.