SQL string tokenizer

There are many ways to tokenize a string in Oracle, most of them involving PL/SQL.
If you wish to use the API, at first glance and based upon its name, DBMS_UTILITY.COMMA_TO_TABLE seems to be a good solution. Well, it is not, because, among others: "The procedure fails if the string between separators is longer than 30 bytes".
You will have to dig a little deeper to find APEX_UTIL.STRING_TO_TABLE, or create your own function.

A good solution, detailed by ALEX NUIJTEN is to use a hierarchical query that uses a regular expression to transform your string into a list:

SQL> select regexp_substr('this,is,a,list','[^,]+',1,rownum) ord_list_element
  2  from dual
  3  connect by level<=length(regexp_replace('this,is,a,list','[^,]+'))+1
  4  ;

ORD_LIST_ELEME
--------------
this
is
a
list

SQL>

From there, you may wish to write your own COMMA_TO_TABLE:

SQL> CREATE OR REPLACE PACKAGE MY_UTILITY
  2  IS
  3    /* To be used instead of dbms_utility.lname_array */
  4    TYPE LNAME_ARRAY IS TABLE OF VARCHAR2(32667) INDEX BY BINARY_INTEGER;
  5    /* Tokenizes a comma separated list. */
  6    PROCEDURE COMMA_TO_TABLE(LIST IN VARCHAR2
  7                            ,TABLEN OUT BINARY_INTEGER
  8                            ,TAB OUT MY_UTILITY.LNAME_ARRAY);
  9  END MY_UTILITY;
 10  /

Package crÚÚ.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY MY_UTILITY IS
  2    /* Tokenizes a comma separated list. */
  3    PROCEDURE COMMA_TO_TABLE(LIST IN VARCHAR2
  4                            ,TABLEN OUT BINARY_INTEGER
  5                            ,TAB OUT MY_UTILITY.LNAME_ARRAY)
  6    IS
  7    BEGIN
  8        select regexp_substr(LIST, '[^,]+',1,rownum) astring
  9        bulk collect into TAB
 10        from dual
 11        connect by level<= length(regexp_replace(LIST, '[^,]+'))+1;
 12        TABLEN := TAB.count;
 13    END COMMA_TO_TABLE;
 14  END MY_UTILITY;
 15  /

Corps de package crÚÚ.

SQL>

Cleanup:

drop package my_utility;