How get single items from a list of values

Sometimes when we develop procedures or functions we have to handle list of values, passed as parameters (for example).
Let's see a simple script helping us to split in single values the list of values, using the powerful WITH clause.

DECLARE

   w_list VARCHAR2(200);

BEGIN        

   w_list := '1;2;3;5';

   FOR R1 IN (WITH VALUE_LIST

                        AS (SELECT SUBSTR(w_list,
                                                     INSTR(';' || w_list || ';', ';', 1, ROW_NUM),

                                                 INSTR(';' || w_list || ';', ';', 1, ROW_NUM + 1) - 
                                                     INSTR(';' || w_list || ';', ';', 1, ROW_NUM) - 1

                                              ) VALUE

                                FROM (SELECT ROWNUM ROW_NUM

                                             FROM DUAL

                                          CONNECT BY LEVEL <= LENGTH(w_list) - LENGTH(REPLACE(w_list, ';', '')) + 1

  )

       )

                   SELECT TRIM(VALUE_LIST.VALUE) VAL

                     FROM VALUE_LIST

)

    LOOP

       DBMS_OUTPUT.PUT_LINE(R1.VAL);  

    END LOOP;                  

END;

The WITH clause allow us to create a materialized subquery, that exposes every single value of the list selectable by a look-up query.