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?