1* select banner from v$version
SQL> /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2     m_msg  VARCHAR2(100) := 'VALUEXX1,VALUEAA2,VALUEBB3,';
  3     v_msg  VARCHAR2(100);
  4     x_len  NUMBER := length(m_msg);
  5     x1     NUMBER := 1;
  6  BEGIN
  7     while x1 <= length(m_msg)
  8     LOOP
  9        Select substr(m_msg,x1,instr(m_msg,',')-1) into v_msg from dual;
 10        x1 := x1+length(v_msg)+1;
 11        dbms_output.put_line(x1||' - '||v_msg);
 12     END LOOP;
 13* END;
SQL> /
10 - VALUEXX1
19 - VALUEAA2
28 - VALUEBB3

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2     m_msg  VARCHAR2(100) := 'VALUEX1,VALUEAA2,VALUEBB3,';  -- here I change from VALUEXX1 -> VALUEX1 
  3     v_msg  VARCHAR2(100);
  4     x_len  NUMBER := length(m_msg);
  5     x1     NUMBER := 1;
  6  BEGIN
  7     while x1 <= length(m_msg)
  8     LOOP
  9        Select substr(m_msg,x1,instr(m_msg,',')-1) into v_msg from dual;
 10        x1 := x1+length(v_msg)+1;
 11        dbms_output.put_line(x1||' - '||v_msg);
 12     END LOOP;
 13* END;
SQL> /
9 - VALUEX1
17 - VALUEAA
25 - ,VALUEB
28 - 3,

PL/SQL procedure successfully completed.

SQL> 

SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'

PL/SQL procedure successfully completed.

SQL> select substr(:mylist,
  2                instr(','||:mylist||',', ',', 1, rn),
  3                instr(','||:mylist||',', ',', 1, rn+1)
  4                - instr(','||:mylist||',', ',', 1, rn) - 1) value
  5  from ( select level rn from dual 
  6         connect by level 
  7                      <= length(:mylist)-length(replace(:mylist,',',''))+1
  8       )
  9  /
VALUE
-----------------------------------------------------------
5
11
13
22
23
31
44
45

SQL> declare
  2    type string_array is varray(100) of varchar2(100);
  3    a_mylist string_array;
  4    mylist   VARCHAR2(100) := 'VALUE_X1,VALUE_XX2,VALUE_XXX3';
  5    --
  6    cursor c1 is
  7           select substr(mylist,
  8                instr(','||mylist||',', ',', 1, rn),
  9                instr(','||mylist||',', ',', 1, rn+1)
 10                - instr(','||mylist||',', ',', 1, rn) - 1) value
 11            from ( select level rn from dual
 12                 connect by level 
 13                       <= length(mylist)-length(replace(mylist,',',''))+1
 14                 );
 15  begin
 16    open c1;
 17    fetch c1 bulk collect into a_mylist;
 18    close c1;
 19    for indx in a_mylist.first..a_mylist.last loop
 20        dbms_output.put_line ( a_mylist(indx) );
 21    end loop;
 22  end;
 23  .
SQL> /
VALUE_X1
VALUE_XX2
VALUE_XXX3

PL/SQL procedure successfully completed.