oracle – Convert comma separated string to array in PL/SQL

oracle – Convert comma separated string to array in PL/SQL

here is another easier option

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

Oracle provides the builtin function DBMS_UTILITY.COMMA_TO_TABLE.

Unfortunately, this one doesnt 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.

oracle – Convert comma separated string to array in PL/SQL

We can never run out of alternatives of doing the same thing differently, right?
I recently found this is pretty handy:

DECLARE
   BAR   VARCHAR2 (200) := 1,2,3;
BEGIN
   FOR FOO IN (    SELECT REGEXP_SUBSTR (BAR,
                                         [^,]+,
                                         1,
                                         LEVEL)
                             TXT
                     FROM DUAL
               CONNECT BY REGEXP_SUBSTR (BAR,
                                         [^,]+,
                                         1,
                                         LEVEL)
                             IS NOT NULL)
   LOOP
      DBMS_OUTPUT.PUT_LINE (FOO.TXT);
   END LOOP;
END;

Outputs:

1
2
3

Leave a Reply

Your email address will not be published. Required fields are marked *