Example of splitting strings using SQL_MACRO for Oracle 19

query_example.sql

with t as (select 'aaa,asdf,2,3,3,4,5,,123,,,zz' s from dual)
select *
from t outer apply str_split(t.s, ',')
/

str_split.sql

create or replace function str_split(str varchar2, delimiter varchar2)
  return varchar2 sql_macro
is
begin
  return q'{
    select
      level as n
      ,regexp_substr(
         s
        ,'([^'||delimiter||']*)['||delimiter||']'
        ,1
        ,level
        ,''
        ,1) as substring
    from  (select str||delimiter as s from dual) v
    connect by level<=regexp_count(s,delimiter)
  }';
end;
/

with t as (select 'aaa,asdf,2,3,3,4,5,,123,,,zz' s from dual)
select *
from t
     outer apply
        xmltable(
           'tokenize($STR, $DELIM)'
           passing t.s as str
                 , ',' as delim
           columns
             n for ordinality 
            ,substring varchar2(100) path '.'
        )
/

https://dbfiddle.uk/DBcKnVBJ