I've got interesting question.
How can I turn '1,4' into enum 1,4 to be able to pass it in expression like this:
SELECT DICT_ID AS ITEM_ID FROM DICTIONARY WHERE SERIAL_NUMBER IN (#ARGUMENT#)
Where #ARGUMENT# is '1,4'.
In short, I want to make this query work like if we've passed not a string, but numbers.
Why am I passing '1,4' insteard of just passing numbers - well not me passing this string in query, but some application.
I can edit query, but not #ARGUMENT#
Something like:
select dict_id as item_id
from dictionary d
where serial_number in (
select to_number(regexp_substr ('1,2,3,4','[^,]+',1,level)) as id
from dual
connect by regexp_substr ('1,2,3,4','[^,]+',1,level) is not null
)
Note: I found this on SO some time ago. But I can't find the link any more to give credits to the original author. If anyone knows, I'll gladly replace this with a link to the original
SELECT
DICT_ID AS ITEM_ID
FROM
DICTIONARY
WHERE
INSTR(','||#ARGUMENT#||',', ','||SERIAL_NUMBER||',') > 0
