oracle - Is there a way to accept comma separated input in PLSQL

Question

I am trying to confirm if it's acceptable to provide a comma separated input through a prompt in PLSQL query. For example the input to be like order_no = 12345;67890;09876

Thanks in advance!

Here is my code:

select order_no
from CUSTOMER_ORDER_JOIN
where order_no like nvl('&Order_no', '%')

question from:https://stackoverflow.com/questions/66066628/is-there-a-way-to-accept-comma-separated-input-in-plsql

Answer 1

Generally speaking, no, you can't do that because Oracle considers string you enter as a whole. For example, this query returns nothing because there's no job whose name is Clerk,Manager (both values).

SQL> with emp (ename, job) as
  2    (select 'Smith', 'Clerk'    from dual union all
  3     select 'Allen', 'Salesman' from dual union all
  4     select 'Ward' , 'Salesman' from dual union all
  5     select 'Jones', 'Manager'  from dual
  6    )
  7  select ename, job
  8  from emp
  9  where job in ('&par_jobs');
Enter value for par_jobs: Clerk,Manager

no rows selected

SQL>

However, if you have Apex installed in your database, and that Apex version supports apex_string.split function, then you can do something which is close to what you're looking for:

SQL> with emp (ename, job) as
  2    (select 'Smith', 'Clerk'    from dual union all
  3     select 'Allen', 'Salesman' from dual union all
  4     select 'Ward' , 'Salesman' from dual union all
  5     select 'Jones', 'Manager'  from dual
  6    )
  7  select ename, job
  8  from emp
  9  where job in (select * from table(apex_string.split('&par_jobs', ',')));
Enter value for par_jobs: Clerk,Manager

ENAME JOB
----- --------
Smith Clerk
Jones Manager

SQL>

Otherwise, you'll have to type a little bit more to achieve that, e.g.

SQL> with emp (ename, job) as
  2    (select 'Smith', 'Clerk'    from dual union all
  3     select 'Allen', 'Salesman' from dual union all
  4     select 'Ward' , 'Salesman' from dual union all
  5     select 'Jones', 'Manager'  from dual
  6    )
  7  select ename, job
  8  from emp
  9  where job in (select regexp_substr('&&par_jobs', '[^,]+', 1, level)
 10                from dual
 11                connect by level <= regexp_count('&&par_jobs', ',') + 1
 12               );
Enter value for par_jobs: Clerk,Manager

ENAME JOB
----- --------
Smith Clerk
Jones Manager

SQL>

---

Basically, YMMV.