This alternative implementation of a varying IN list, which uses logic described at String to Rows: MODEL Method, only works only on Oracle 10g or above.
variable d varchar2
execute :d := ','
variable p varchar2(100)
execute :p := '1,3,5'
select *
from t
where key in
( select to_number( val )
from dual
model
return updated rows
dimension by ( 0 as position )
measures ( :p || :d as val )
rules upsert
( val
[ for position
from 1
to length( regexp_replace( val[0], '[^'||:d||']', null ) )
increment 1
] = rtrim( regexp_substr( val[0], '.*?' || :d, 1, cv(position) ), :d )
)
)
order by 1 ;
KEY C
---------- ----------
1 v1
3 v3
5 v5
Duplicates in the IN list produce the same result as if there were no duplicates.
execute :p := '1,3,3,3,3,5' /
KEY C
---------- ----------
1 v1
3 v3
5 v5
The query accepts IN lists with spaces.
execute :p := '1, 2, 3, 4, 5' /
KEY C
---------- ----------
1 v1
2 v2
3 v3
4 v4
5 v5
If the IN list is null no rows are returned.
execute :p := null PL/SQL procedure successfully completed. / no rows selected
For quoted string lists like 'v1', 'v3', 'v5' we can use a parsing technique described at String to Columns: Quoted Values - 10g+.
-- the delimiter is a single quote this time
execute :d := ''''
execute :p := q'['v1', 'v3', 'v5']'
select *
from t
where c in
( select val
from dual
model
return updated rows
dimension by ( 0 as position )
measures ( :P as val )
rules upsert
( val
[ for position
from 1
to length( regexp_replace( val[0], '[^'||:d||']', null ) )
increment 1
] = TRIM( :D FROM REGEXP_SUBSTR( VAL[0], :D || '.*?' || :D, 1, CV(POSITION) ) )
)
)
order by 1 ;
KEY C
---------- ----------
1 v1
3 v3
5 v5