Convert comma separated string to array in PL/SQL

I was looking for a similar solution where I had multi-byte characters (hyphen, whitespace, underscore) in comma separated lists. So dbms_utility.comma_to_table didn't work for me.

declare
  curr_val varchar2 (255 byte);
  input_str varchar2 (255 byte);
  remaining_str varchar2 (255 byte);
begin
  remaining_str := input_str || ',dummy';  -- this value won't output
  while (regexp_like (remaining_str, '.+,.+'))
  loop
    curr_val := substr (remaining_str, 1, instr (remaining_str, ',') - 1);
    remaining_str = substr (remaining_str, instr (remaining_str, ',') + 1);
    dbms_output.put_line (curr_val);
  end loop;
end;

This is not an expert answer so I hope someone would improve this answer.

The solution presented below by Stewart Ashton in this link is pretty handy. He eliminates the need for the value list to be integer, so you can use string list.

In the WITH clause, he surrounds the values with single quotes then converts it to a table having a single column of type VARCHAR2. https://stewashton.wordpress.com

with data as (
  select '"'||replace(:txt, ',', '","')||'"' str from dual
)
select xmlcast(column_value as varchar2(4000)) subs
from data, xmltable(str);

declare
seprator varchar2(1):=',';
dosweeklist varchar2(4000):='a,b,c';
begin
for i in (SELECT  SUBSTR(dosweeklist,
                         case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end,
                         NVL(NULLIF(INSTR(dosweeklist,seprator,1,LEVEL),0),length(dosweeklist)+1) - case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end) dat 
          FROM dual
          CONNECT BY LEVEL <= LENGTH(dosweeklist) - LENGTH(REPLACE(dosweeklist,seprator,'')) +1)
loop
dbms_output.put_line(i.dat);
end loop;
end;
/

so select query only in for loop can do the trick, by replacing dosweeklist as your delimited string and seprator as your delimited character.

Lets see output

a

b

c