oracle-databaseplsqltokenize

Convert comma separated string to array in PL/SQL


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.


Solution

  • 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.