This is actually prettier and more elegant than I usually get for PL/SQL.
, which you may find in Feuerstein and Pribyl's Oracle PL/SQL Programming from O'Reilly, 3rd edition, page 216, or implement yourself. Incidentally, one bug I had along the way was using SUBSTR here without thinking about what I really wanted to do.
Generalizing to take any arbitrary character to split on as a parameter is left as an exercise to the reader. As is generalizing to take any arbitrary string, followed by generalizing to take any arbitrary regular expression.
FUNCTION comma_split(p_text VARCHAR2)
WHILE v_lastpos <= LENGTH(p_text)
:= v_count + 1;
:= INSTR(p_text, ',', 1, v_count);
IF v_pos = 0
:= LENGTH(p_text) + 1;
:= betwnstr(p_text, v_lastpos + 1, v_pos - 1);