Programs Knowledge

create or replace type array_type as varray(2000) of varchar2(1000);

create or replace function STRING_SPLIT(str in varchar2, delim in varchar2 default '.')
    return array_type
    as
        l_str long default str || delim;
        l_n number;
        l_data array_type := array_type();
    begin
        loop
            l_n := instr(l_str, delim);
            exit when(nvl(l_n,0) = 0);
            l_data.extend;
            l_data(l_data.count) := ltrim(rtrim(substr(l_str, 1, l_n - 1)));
            l_str := substr(l_str, l_n + length(delim));
        end loop;
        return l_data;
    end STRING_SPLIT;