Getting invalid number exception when passing multiple numbers in IN clause as parameter to Oracle query

Question

Getting invalid number exception when passing multiple numbers as input in IN clause as parameter at run time. Kindly help me to resolve this problem.

I have tried it for string column its working fine, also tried the same for number column by passing the values directly, its working fine. If I pass the values as parameters its not working.

Select Name, Id from Employee where Id IN (:Param)

Passed 1,2,3 as input when it prompts for :Param value. it throws exception.

In cause if i pass the value directly, its working.

Select Name, Id from Employee where Id IN (1,2,3)

I want to filter the data based on number field by passing the number values in IN condition at run time. How to achieve this in Oracle.

where Id IN (:Param) gets translated into where Id IN ('1,2,3') That does not work. Look at stackoverflow.com/questions/337704/… — juergen d
– juergen d, Commented Aug 9, 2019 at 6:55

Popeye · Accepted Answer · 2019-08-09 07:04:50Z

3

If you want to make it work in such a situation then you need to separate those values into columns and then use it in IN clause like the following query:

SELECT
    NAME,
    ID
FROM
    EMPLOYEE
WHERE
    ID IN (
        SELECT
    REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL)
FROM
    DUAL
CONNECT BY
    REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) IS NOT NULL
    );

Replace '1,2,3' with :Param to use it in your code

Cheers!!

edited Aug 9, 2019 at 7:04

answered Aug 9, 2019 at 6:59

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

1 Comment

Popeye Over a year ago

If that answer solved your question then please accept it, so that your question is marked as resolved.

Ed Gibbs · Accepted Answer · 2019-08-10 04:01:02Z

@Tejash's answer is brilliant but the query isn't optimizable. That matters if the Employee table will be large.

There's presumably an index/PK on Employee.Id. Using REGEXP_SUBSTR means Oracle will ignore the index and scan the entire Employee table looking for the IDs, evaluating REGEXP_SUBSTR for each row.

Two optimizable approaches come to mind.

1. Don't use parameters for the Employee ID list

I know, sacrilege. But if the Employee ID list is a collection of valid numbers, for example List<int>, int[], IEnumerable<long>, etc., then the input is cleansed and there's no attack surface for SQL Injection.

// employeeIds is the list of IDs; must be vetted as valid numbers
string ids = string.Join(",", employeeIds);
string query = $"Select Name, Id from Employee where Id IN ({ids})";

Assuming employee IDs 1, 2 and 3, query will have the following value:

Select Name, Id from Employee where Id IN (1, 2, 3)

2. Create a separate parameter for each Employee ID

Required if the Employee IDs aren't a collection of valid numbers, meaning any of the following:

Employee IDs can have letters.
Employee IDs are numbers but they're not validated anywhere.
Employee IDs are numbers validated on the client side but not the server side.

Note: the code for setting parameter values may vary depending on the data access library used.

// employeeIds is a list of IDs; could be any value
int parmNumber = 0;
List<string> parms = new List<string>();
foreach (string id in employeeIds) {
  string parmName = $":id{parmNumber++}"; // id0 or id1 or etc.
  parms.Add(parmName);
  // "command" is the OracleCommand/DbCommand/whatever instance
  command.Parameters.Add(new OracleParameter(parmName, id)); 
}
string ids = string.Join(",", parms); // ":id0, :id1, etc."
string query = $"Select Name, Id from Employee where Id IN ({ids})";

Assuming employee IDs 1, 2 and 3, query will have the following value, and the parameter values will be set to 1, 2 and 3 respectively:

Select Name, Id from Employee where Id IN (:id0, :id1, :id2)

Collectives™ on Stack Overflow

Getting invalid number exception when passing multiple numbers in IN clause as parameter to Oracle query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related