1

Below is my query and i am passing a string value 1,2 as bind value but it is showing an error as it is not a valid number. I know IN accepts only number but here i need to pass the string value

SELECT  e.*
FROM    employee_detail e
WHERE   e.emp_id IN (:emp_id)
manikandan
  • 139
  • 1
  • 2
  • 15
  • Are you after dynamic in-lists? If so, maybe [this](https://oracle-base.com/articles/misc/dynamic-in-lists) would be of use to you? – Boneist Jun 27 '17 at 10:50
  • I'm afraid you must tell us more. I tried to use string- bind variables via Lazarus and there was no Problem. I got it right: You want to pass exactly 1 string value "1,2" to this query? – am2 Jun 27 '17 at 10:59
  • qry.SQL.Text := 'SELECT * FROM BLABLA WHERE BLA IN (:P1)'; qry.ParamByName('P1').AsString := '1,2'; qry.Active:=true; – am2 Jun 27 '17 at 11:01
  • Simply put, you can't do that. Oracle does not allow comma-separated lists of numbers to be passed as strings. – Bob Jarvis - Слава Україні Jun 27 '17 at 11:06
  • [How to load a large number of strings to match with oracle database?](https://stackoverflow.com/a/34699771/1509264) gives an example of how to bind an array of values to an Oracle collection using Java. Linked off that question are multiple others which deal with similar topics to this question. – MT0 Jun 27 '17 at 14:59

2 Answers2

1

In is used with list of values or subqueries.

You can convert a comma separeted string into a subquery, by using the following hack:

 SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level))   
    FROM (SELECT  :emp_id temp FROM DUAL)
    CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')

Here the 1,2,3 sting will be converted into subquery that returns 3 rows.

So, the end result, for your case, could be something like this: 

SELECT  e.*
FROM    employee_detail e
WHERE   e.emp_id in (
SELECT decode(:emp_id,null,  (select  e.emp_id from dual) 
,TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)) )  
    FROM (SELECT  :emp_id temp FROM DUAL)
    CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+'))

Note that in this case In will return true if :emp_id is null and this was deliberatly achieved by using decode function.

PKey
  • 3,715
  • 1
  • 14
  • 39
  • `IN` is not used with collections it is [used with either an expression list or a subquery](https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm). `MEMBER OF` is [used with collections](https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjcol.htm#sthref512). – MT0 Jun 27 '17 at 14:53
  • @MT0 OK, thanks for the correction, regarding the term I used. It is indeed subquery. – PKey Jun 27 '17 at 15:24
1

You can use string comparisons instead of an IN condition.

select ...
from   ...
where  ',' || :emp_id || ',' like '%,' || to_char(emp_id) || ',%'
;