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:
declare
p_empno_list constant varchar2(20) := '7369,7499,7839,7902';
begin
for r in (
select * from emp
where empno in (p_empno_list)
)
loop
dbms_output.put_line(rpad(r.empno,9) || r.ename);
end loop;
end;
/
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?
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.