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 https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause — juergen d, Aug 09 '19 at 06:55

Popeye · Accepted Answer · 2019-08-09T07:04:50.043

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 09 '19 at 07:04

answered Aug 09 '19 at 06:59

Popeye

35,427
4
10
31

If that answer solved your question then please [accept](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) it, so that your question is marked as resolved. – Popeye Aug 10 '19 at 07:02

score 0 · Answer 2 · answered Aug 10 '19 at 04:01

@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)

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

2 Answers2