prompt
prompt Creating type STR_ARRAY
prompt ====================
prompt
create or replace type str_array is table of varchar2(4000);
/

prompt
prompt Creating package STR
prompt ====================
prompt
create or replace package Str is

   function Split(sText in varchar2, sDel in varchar2 := ',') return str_array;
   function Split(sText in clob, sDel in varchar2 := ',') return str_array;
   
end Str;
/

prompt
prompt Creating package body STR
prompt =========================
prompt
create or replace package body Str is

function Split(sText in varchar2, sDel in varchar2 := ',') return str_array is
    nStartIdx pls_integer := 1;
    nEndIdx pls_integer := 1;
    oRet str_array := str_array();
begin

    if sText is null then return oRet; end if;

    loop

         nEndIdx := instr(sText, sDel, nStartIdx);

         if nEndIdx > 0 then

            oRet.Extend;
            oRet(oRet.last) := substr(sText, nStartIdx, nEndIdx - nStartIdx);
            nStartIdx := nEndIdx + 1;

         else

            oRet.Extend;
            oRet(oRet.last) := substr(sText, nStartIdx);
            exit;

         end if;

    end loop;

    return oRet;

end Split;

function Split(sText in clob, sDel in varchar2 := ',') return str_array is
    nStartIdx pls_integer := 1;
    nEndIdx pls_integer := 1;
    oRet str_array := str_array();
begin

    if sText is null then return oRet; end if;
    if dbms_lob.getlength(sText) = 0 then return oRet; end if;

    loop

         nEndIdx := dbms_lob.instr(sText, sDel, nStartIdx);

         if nEndIdx > 0 then

            oRet.Extend;
            oRet(oRet.last) := dbms_lob.substr(sText, nEndIdx - nStartIdx, nStartIdx);
            nStartIdx := nEndIdx + length(sDel);

         else

            oRet.Extend;
            oRet(oRet.last) := dbms_lob.substr(lob_loc => sText, offset => nStartIdx);
            exit;

         end if;

    end loop;

    return oRet;

end Split;

end Str;
/