Convert comma separated string to array in PL/SQL

Question

How do I convert a comma separated string to a array?

I have the input '1,2,3' , and I need to convert it into an array.

Answer 1

Oracle provides the builtin function DBMS_UTILITY.COMMA_TO_TABLE.

Unfortunately, this one doesn't work with numbers:

SQL> declare   2    l_input varchar2(4000) := '1,2,3';   3    l_count binary_integer;   4    l_array dbms_utility.lname_array;   5  begin   6    dbms_utility.comma_to_table   7    ( list   => l_input   8    , tablen => l_count   9    , tab    => l_array  10    );  11    dbms_output.put_line(l_count);  12    for i in 1 .. l_count  13    loop  14      dbms_output.put_line  15      ( 'Element ' || to_char(i) ||  16        ' of array contains: ' ||  17        l_array(i)  18      );  19    end loop;  20  end;  21  / declare * ERROR at line 1: ORA-00931: missing identifier ORA-06512: at "SYS.DBMS_UTILITY", line 132 ORA-06512: at "SYS.DBMS_UTILITY", line 164 ORA-06512: at "SYS.DBMS_UTILITY", line 218 ORA-06512: at line 6 

But with a little trick to prefix the elements with an 'x', it works:

SQL> declare   2    l_input varchar2(4000) := '1,2,3';   3    l_count binary_integer;   4    l_array dbms_utility.lname_array;   5  begin   6    dbms_utility.comma_to_table   7    ( list   => regexp_replace(l_input,'(^|,)','\1x')   8    , tablen => l_count   9    , tab    => l_array  10    );  11    dbms_output.put_line(l_count);  12    for i in 1 .. l_count  13    loop  14      dbms_output.put_line  15      ( 'Element ' || to_char(i) ||  16        ' of array contains: ' ||  17        substr(l_array(i),2)  18      );  19    end loop;  20  end;  21  / 3 Element 1 of array contains: 1 Element 2 of array contains: 2 Element 3 of array contains: 3  PL/SQL procedure successfully completed. 

Regards, Rob.

Answer 2

here is another easier option

select to_number(column_value) as IDs from xmltable('1,2,3,4,5');