I have a simple select query:
SELECT A
FROM B
WHERE A IN :conditions
where A is a char.
If i do the full query as:
SELECT A
FROM B
WHERE A IN ('A','E','I','O','U')
it works
but if i use the parameter, when i try to execute with ORACLE SQL Developer i cannot get it to work.
I've already tried to submit the parameter as:
1* ('A','E','I','O','U')
2* 'A','E','I','O','U'
3* A,E,I,O,U
and query returns no results
What's the correct syntax to do so?
Thank U all
You may use a substitution variable in SQL Developer (or in SQL* Plus). Use double quotes while defining.
Test data
create table b as select 'A' as a from dual union all
select 'I' as a from dual union all
select 'U' as a from dual
Execution
define params = "'A','E','I','O','U'"
select A from B where A in (¶ms);
Result
old:select A from B where A in (¶ms)
new:select A from B where A in ('A','E','I','O','U')
A
-
A
I
U
Passing comma separated string as bind variable for VO query's IN operator
This problem vexed me for years before I discovered this solution.
I used this technique to build an object search at the CLI, where I pass in a list of schemas to a :BIND
SELECT owner,
object_name,
object_type
FROM all_objects
WHERE object_name LIKE :SEARCH
AND owner NOT IN (
'SYS',
'MDSYS',
'DBSNMP',
'SYSTEM',
'DVSYS',
'APEX_050100',
'PUBLIC',
'ORDS_METADATA',
'APEX_LISTENER'
)
AND object_type IN (
SELECT regexp_substr(:bind_ename_comma_sep_list,'[^,]+',1,level)
FROM dual CONNECT BY
regexp_substr(:bind_ename_comma_sep_list,'[^,]+',1,level) IS NOT NULL
)
ORDER BY owner,
object_name,
object_type;
I Found a way to do it for Oracle based on this post: https://sqlstudies.com/2013/04/08/how-do-i-use-a-variable-in-an-in-clause/
Would be something like this:
SELECT A
FROM B
WHERE ',' || :conditions || ',' LIKE '%,' || A || ',%'
Probably it is not the best performance option but solves the issue
