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

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

CREATE OR REPLACE FUNCTION Fun_Strsplit(p_Value VARCHAR2,

                                        p_Split VARCHAR2 := ',')

--usage: select * from table(fun_strsplit('1,2,3,4,5'))

 RETURN Strsplit_Type

  PIPELINED IS

  v_Idx       INTEGER;

  v_Str       VARCHAR2(500);

  v_Strs_Last VARCHAR2(4000) := p_Value;

BEGIN

  LOOP

    v_Idx := Instr(v_Strs_Last, p_Split);

    EXIT WHEN v_Idx = 0;

    v_Str       := Substr(v_Strs_Last, 1, v_Idx - 1);

    v_Strs_Last := Substr(v_Strs_Last, v_Idx + 1);

    PIPE ROW(v_Str);

  END LOOP;

  PIPE ROW(v_Strs_Last);

  RETURN;

END Fun_Strsplit;