Nodos Nodos - 6 months ago 34
PHP Question

Oracle use a string inside in function

I have a select statement where I use IN function, and inside the IN function i have a can I ignore the single quotes?

Select * from employ where id = 12 and org_id in ({$id})
$id = '12,13'



in is used with collections, so your input string first should be converted into collection (by breaking into rows, based on comma delimiter)

Try something like this

Select * from employ where id = 12 and org_id in (
SELECT decode(:input_id,null,  (select  employ.org_id from dual) 
,TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)) )  
    FROM (SELECT  :input_id temp FROM DUAL)
    CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')

by the way, this org_id in () will return true if :input_id is null.

Another approach would be to construct the whole query as a string first and then execute it either with execute immediate or through php. However this could raise sql injection concerns.