Split function in Oracle

Source code:

Create or replace type ty_str_split is table of VARCHAR2 (4000 );

Create or replace function fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
J INT: = 0;
I INT: = 1;
Len INT: = 0;
Len1 INT: = 0;
Str VARCHAR2 (4000 );
Str_split ty_str_split: = ty_str_split ();
BEGIN
Len: = LENGTH (p_str );
Len1: = LENGTH (p_delimiter );

While j <Len
Loop
J: = instr (p_str, p_delimiter, I );

If J = 0
Then
J: = Len;
STR: = substr (p_str, I );
Str_split.extend;
Str_split (str_split.count): = STR;

If I> = Len
THEN
EXIT;
End if;
ELSE
Str: = SUBSTR (p_str, I, j-I );
I: = j + len1;
Str_split.EXTEND;
Str_split (str_split.COUNT): = str;
End if;
End loop;

RETURN str_split;
END fn_split;
/

Test:

DECLARE
CURSOR c
IS
SELECT *
From table (CAST (fn_split ('1; 12; 123; 1234; 12345 ','; ') AS ty_str_split
)
);

R c % ROWTYPE;
BEGIN
OPEN c;

LOOP
FETCH c INTO r;

Exit when c % NOTFOUND;
DBMS_OUTPUT.put_line (r. column_value );
End loop;

CLOSE c;
END;
/

　

Result:
1
12
123
1234
12345