Всё очень просто.

1) Создаём тип данных:

create or replace type split_tbl as table of varchar2(32767);

2) Создаём функцию :

create or replace function split
(
 p_list varchar2,
 p_del varchar2 := ','
) return split_tbl pipelined
is
 l_idx pls_integer;
 l_list varchar2(32767) := p_list;
 l_value varchar2(32767);
begin
 loop
  l_idx := instr(l_list,p_del);
  if l_idx > 0 then
   pipe row(substr(l_list,1,l_idx-1));
   l_list := substr(l_list,l_idx+length(p_del));
  else
   pipe row(l_list);
   exit;
  end if;
 end loop;
 return;
end split;

3) Тестируем:

select * from table (split('AAAAZBBBBZCCCCCZXXX','Z'))

4) Получаем:

COLUMN_VALUE
AAAA
BBBB
CCCCC
XXX

Коротко, ясно и лаконично.