Oracle 18c

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

type result_str is table of varchar2(100);

function unpack_list(
  string_in in varchar2
) return result_str pipelined;

end p;
/
create or replace package body p as

function unpack_list(
  string_in in varchar2
) return result_str pipelined
is
begin
  
  for r in (
    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)
  loop
    pipe row (r.str);
  end loop;

  RETURN;

end;

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


COLUMN_VALUE


one1

two2


declare
  l_result_str p.result_str;
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;
/
ORA-06550: line 5, column 10:
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope