oracle - How can we enter many values in a SQL parameter - SQL Developer?

Question

In SQL Developer, we can use parameters in order to test our query with different values - for example:

I have a table called Fruits (code, name). I want to retrieve code's apples.

SELECT * 
FROM fruits 
WHERE name IN (:PM_NAME)

It works correctly when I fill out one value (in this case :PM_NAME equal apple)

But when I want to fill out many values it doesn't work! I've tried these forms and these separators but still..

apple;orange

'apple';'orange'

('apple','orange')

['apple','orange']

"apple","orange"

In a nutshell what's the correct format to fill out multiple values in a SQL parameter in SQL Developer ?

question from:https://stackoverflow.com/questions/65832480/how-can-we-enter-many-values-in-a-sql-parameter-sql-developer

Answer 1

I can't take credit for this 'trick' but the solution lies in a regular expression.

I want to search on multiple types of objects, fed to a :bind, and used in a WHERE clause.

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 first learned of this 'trick' or technique from here.

So your query would look like this

SELECT * 
FROM fruits 
WHERE name IN (
    SELECT regexp_substr(:PM_NAME,'[^,]+',1,level)
      FROM dual CONNECT BY
        regexp_substr(:PM_NAME,'[^,]+',1,level) IS NOT NULL
)   

When you're prompted for values by SQL Developer, don't quote the strings, just comma separate them. Also, no spaces.

So in the input box, enter apple,orange

And I suppose if you want ; vs , then update the regex call as needed.