Split

CREATE OR REPLACE TYPE type_split IS TABLE OF VARCHAR2 (4000)


create or replace function split(p_list varchar2,p_sep varchar2 := ',') return type_split pipelined
IS
l_idx pls_integer;
v_list varchar2(4000) := p_list;
begin
      loop
           l_idx := instr(v_list,p_sep);
           if l_idx > 0 then
               pipe row(substr(v_list,1,l_idx-1));
               v_list := substr(v_list,l_idx+length(p_sep));
           else
                pipe row(v_list);
                exit;
           end if;

      end loop;


      return;
end split;



select * from table(split('1,2,3,4,5'));