troubleshooting Question

Convert CSV to list in oracle for IN-Clause

Avatar of Arikkan
ArikkanFlag for United States of America asked on
ROracle DatabaseStatistical Packages
6 Comments6 Solutions300 ViewsLast Modified:
My procedure is passed a comma-separated list of IDs, for example 7369,7499,7839,7902. I tried to use it in my code like this:

   p_empno_list constant varchar2(20) := '7369,7499,7839,7902';
   for r in (
      select * from emp
      where  empno in (p_empno_list)
      dbms_output.put_line(rpad(r.empno,9) || r.ename);
   end loop;
but it just gives me an error:1 When SQL is expecting a character string such as SMITH, but is passed a comma-separated list such as SMITH,JONES,FORD,MILLER, no error is produced, but the query simply returns no rows.

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

How can i convert CSV to list so that it can be accepted in IN- clause?
Join our community to see this answer!
Unlock 6 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 6 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros