Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create or replace package p as

function unpack_list(
  string_in in varchar2
) return sys.odcivarchar2list;

end p;
/
create or replace package body p as

function unpack_list(
  string_in in varchar2
) return sys.odcivarchar2list 
is
  result_rows sys.odcivarchar2list;
begin
  
  with temp_table as (
  SELECT distinct trim(regexp_substr(string_in, '[^,]+', 1, level)) str
  FROM (SELECT string_in FROM dual) t
  CONNECT BY instr(string_in, ',', 1, level - 1) > 0)
  
  select str bulk collect into result_rows from temp_table;
  
  RETURN result_rows;
  
end;

end p;
/
select * from user_errors;
select * from p.unpack_list('one1, two2')


COLUMN_VALUE


one1

two2


declare
  l_result_str sys.odcivarchar2list;
begin
  l_result_str := p.unpack_list('one1, two2');
  for i in 1..l_result_str.count loop
    dbms_output.put_line(l_result_str(i));
  end loop;
end;
/
1 rows affected

dbms_output:
one1
two2