在 PL/SQL 中将逗号分隔的字符串转换为数组

Question

如何将逗号分隔的字符串转换为数组？

我有输入 '1,2,3' ，我需要将其转换为数组。

Answer 1

我们永远不会用尽不同的选择来做同样的事情，对吗？
我最近发现这非常方便：

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;

输出：

1
2
3

Answer 2

我知道 Stack Overflow 不赞成在没有解释的情况下粘贴 URL，但这个特定页面有一些非常好的选项：

http://www. oratechinfo.co.uk/delimited_lists_to_collections.html

我特别喜欢这个，它将分隔列表转换为可以运行查询的临时表：

/* Create the output TYPE, here using a VARCHAR2(100) nested table type */

SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);
  2  /

Type created.

/* Now, create the function.*/

SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)
  2    RETURN test_type
  3  AS
  4    l_string       VARCHAR2(32767) := p_list || ',';
  5    l_comma_index  PLS_INTEGER;
  6    l_index        PLS_INTEGER := 1;
  7    l_tab          test_type := test_type();
  8  BEGIN
  9    LOOP
 10      l_comma_index := INSTR(l_string, ',', l_index);
 11      EXIT WHEN l_comma_index = 0;
 12      l_tab.EXTEND;
 13      l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
 14      l_index := l_comma_index + 1;
 15    END LOOP;
 16    RETURN l_tab;
 17  END f_convert;
 18  /

Function created.

---

/* Prove it works */

SQL> SELECT * FROM TABLE(f_convert('AAA,BBB,CCC,D'));

COLUMN_VALUE
--------------------------------------------------------------------------------
AAA
BBB
CCC
D

4 rows selected.

Answer 3

简单的代码

    create or replace function get_token(text_is varchar2, token_in number, delim_is varchar2 := ';') return varchar2 is
       text_ls varchar2(2000);
       spos_ln number;
       epos    _ln number;
    begin
       text_ls := delim_is || text_is || rpad(delim_is, token_in, delim_is);
       spos_ln := instr(text_ls, delim_is, 1, token_in);
       epos_ln := instr(text_ls, delim_is, 1, token_in+1);
       return substr(text_ls, spos_ln+1, epos_ln-spos_ln-1);
    end get_token;

Answer 4

是的，令人沮丧的是 dbms_utility.comma_to_table 仅支持逗号分隔列表，并且仅当列表中的元素是有效的 PL/SQL 标识时（因此数字会导致错误）。

我创建了一个通用解析包，可以满足您的需要（粘贴在下面）。它是我的“demo.zip”文件的一部分，该文件是一个包含 2000 多个支持我的培训材料的文件的存储库，所有这些文件都可以在 PL/SQL Obsession 上获得：www.toadworld.com/SF。

问候，
史蒂文·福尔斯坦
www.plsqlchallenge.com
（每日 PL/SQL 测验）

    CREATE OR REPLACE PACKAGE parse
/*
   Generalized delimited string parsing package

   Author: Steven Feuerstein, [email protected]

   Latest version always available on PL/SQL Obsession: 

   www.ToadWorld.com/SF

   Click on "Trainings, Seminars and Presentations" and
   then download the demo.zip file.

   Modification History
      Date          Change
      10-APR-2009   Add support for nested list variations

   Notes:
     * This package does not validate correct use of delimiters.
       It assumes valid construction of lists.
     * Import the Q##PARSE.qut file into an installation of 
       Quest Code Tester 1.8.3 or higher in order to run
       the regression test for this package.

*/
IS
   SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);

   /*
   Each of the collection types below correspond to (are returned by)
   one of the parse functions.

   items_tt - a simple list of strings
   nested_items_tt - a list of lists of strings
   named_nested_items_tt - a list of named lists of strings

   This last type also demonstrates the power and elegance of string-indexed
   collections. The name of the list of elements is the index value for
   the "outer" collection.
   */
   TYPE items_tt IS TABLE OF maxvarchar2_t
                       INDEX BY PLS_INTEGER;

   TYPE nested_items_tt IS TABLE OF items_tt
                              INDEX BY PLS_INTEGER;

   TYPE named_nested_items_tt IS TABLE OF items_tt
                                    INDEX BY maxvarchar2_t;

   /*
   Parse lists with a single delimiter.
   Example: a,b,c,d

   Here is an example of using this function:

   DECLARE
      l_list parse.items_tt;
   BEGIN
      l_list := parse.string_to_list ('a,b,c,d', ',');
   END;
   */
   FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
      RETURN items_tt;

   /*
   Parse lists with nested delimiters.
   Example: a,b,c,d|1,2,3|x,y,z

   Here is an example of using this function:

   DECLARE
      l_list parse.nested_items_tt;
   BEGIN
      l_list := parse.string_to_list ('a,b,c,d|1,2,3,4', '|', ',');
   END;
   */
   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN nested_items_tt;

   /*
   Parse named lists with nested delimiters.
   Example: letters:a,b,c,d|numbers:1,2,3|names:steven,george

   Here is an example of using this function:

   DECLARE
      l_list parse.named_nested_items_tt;
   BEGIN
   l_list := parse.string_to_list ('letters:a,b,c,d|numbers:1,2,3,4', '|', ':', ',');
   END;
   */
   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , name_delim_in  IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN named_nested_items_tt;

   PROCEDURE display_list (string_in IN VARCHAR2
                         , delim_in  IN VARCHAR2:= ','
                          );

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          );

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , name_delim_in  IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          );

   PROCEDURE show_variations;

   /* Helper function for automated testing */
   FUNCTION nested_eq (list1_in    IN items_tt
                     , list2_in    IN items_tt
                     , nulls_eq_in IN BOOLEAN
                      )
      RETURN BOOLEAN;

END parse;
/

CREATE OR REPLACE PACKAGE BODY parse
IS
   FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
      RETURN items_tt
   IS
      c_end_of_list   CONSTANT PLS_INTEGER := -99;
      l_item          maxvarchar2_t;
      l_startloc      PLS_INTEGER := 1;
      items_out       items_tt;

      PROCEDURE add_item (item_in IN VARCHAR2)
      IS
      BEGIN
         IF item_in = delim_in
         THEN
            /* We don't put delimiters into the collection. */
            NULL;
         ELSE
            items_out (items_out.COUNT + 1) := item_in;
         END IF;
      END;

      PROCEDURE get_next_item (string_in         IN     VARCHAR2
                             , start_location_io IN OUT PLS_INTEGER
                             , item_out             OUT VARCHAR2
                              )
      IS
         l_loc   PLS_INTEGER;
      BEGIN
         l_loc := INSTR (string_in, delim_in, start_location_io);

         IF l_loc = start_location_io
         THEN
            /* A null item (two consecutive delimiters) */
            item_out := NULL;
         ELSIF l_loc = 0
         THEN
            /* We are at the last item in the list. */
            item_out := SUBSTR (string_in, start_location_io);
         ELSE
            /* Extract the element between the two positions. */
            item_out :=
               SUBSTR (string_in
                     , start_location_io
                     , l_loc - start_location_io
                      );
         END IF;

         IF l_loc = 0
         THEN
            /* If the delimiter was not found, send back indication
               that we are at the end of the list. */

            start_location_io := c_end_of_list;
         ELSE
            /* Move the starting point for the INSTR search forward. */
            start_location_io := l_loc + 1;
         END IF;
      END get_next_item;
   BEGIN
      IF string_in IS NULL OR delim_in IS NULL
      THEN
         /* Nothing to do except pass back the empty collection. */
         NULL;
      ELSE
         LOOP
            get_next_item (string_in, l_startloc, l_item);
            add_item (l_item);
            EXIT WHEN l_startloc = c_end_of_list;
         END LOOP;
      END IF;

      RETURN items_out;
   END string_to_list;

   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN nested_items_tt
   IS
      l_elements   items_tt;
      l_return     nested_items_tt;
   BEGIN
      /* Separate out the different lists. */
      l_elements := string_to_list (string_in, outer_delim_in);

      /* For each list, parse out the separate items
         and add them to the end of the list of items
         for that list. */   
      FOR indx IN 1 .. l_elements.COUNT
      LOOP
         l_return (l_return.COUNT + 1) :=
            string_to_list (l_elements (indx), inner_delim_in);
      END LOOP;

      RETURN l_return;
   END string_to_list;

   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , name_delim_in  IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN named_nested_items_tt
   IS
      c_name_position constant pls_integer := 1;
      c_items_position constant pls_integer := 2;
      l_elements          items_tt;
      l_name_and_values   items_tt;
      l_return            named_nested_items_tt;
   BEGIN
      /* Separate out the different lists. */
      l_elements := string_to_list (string_in, outer_delim_in);

      FOR indx IN 1 .. l_elements.COUNT
      LOOP
         /* Extract the name and the list of items that go with 
            the name. This collection always has just two elements:
              index 1 - the name
              index 2 - the list of values
         */
         l_name_and_values :=
            string_to_list (l_elements (indx), name_delim_in);
         /*
         Use the name as the index value for this list.
         */
         l_return (l_name_and_values (c_name_position)) :=
            string_to_list (l_name_and_values (c_items_position), inner_delim_in);
      END LOOP;

      RETURN l_return;
   END string_to_list;

   PROCEDURE display_list (string_in IN VARCHAR2
                         , delim_in  IN VARCHAR2:= ','
                          )
   IS
      l_items   items_tt;
   BEGIN
      DBMS_OUTPUT.put_line (
         'Parse "' || string_in || '" using "' || delim_in || '"'
      );

      l_items := string_to_list (string_in, delim_in);

      FOR indx IN 1 .. l_items.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('> ' || indx || ' = ' || l_items (indx));
      END LOOP;
   END display_list;

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          )
   IS
      l_items   nested_items_tt;
   BEGIN
      DBMS_OUTPUT.put_line(   'Parse "'
                           || string_in
                           || '" using "'
                           || outer_delim_in
                           || '-'
                           || inner_delim_in
                           || '"');
      l_items := string_to_list (string_in, outer_delim_in, inner_delim_in);


      FOR outer_index IN 1 .. l_items.COUNT
      LOOP
         DBMS_OUTPUT.put_line(   'List '
                              || outer_index
                              || ' contains '
                              || l_items (outer_index).COUNT
                              || ' elements');

         FOR inner_index IN 1 .. l_items (outer_index).COUNT
         LOOP
            DBMS_OUTPUT.put_line(   '> Value '
                                 || inner_index
                                 || ' = '
                                 || l_items (outer_index) (inner_index));
         END LOOP;
      END LOOP;
   END display_list;

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , name_delim_in  IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          )
   IS
      l_items   named_nested_items_tt;
      l_index   maxvarchar2_t;
   BEGIN
      DBMS_OUTPUT.put_line(   'Parse "'
                           || string_in
                           || '" using "'
                           || outer_delim_in
                           || '-'
                           || name_delim_in
                           || '-'
                           || inner_delim_in
                           || '"');
      l_items :=
         string_to_list (string_in
                       , outer_delim_in
                       , name_delim_in
                       , inner_delim_in
                        );

      l_index := l_items.FIRST;

      WHILE (l_index IS NOT NULL)
      LOOP
         DBMS_OUTPUT.put_line(   'List "'
                              || l_index
                              || '" contains '
                              || l_items (l_index).COUNT
                              || ' elements');

         FOR inner_index IN 1 .. l_items (l_index).COUNT
         LOOP
            DBMS_OUTPUT.put_line(   '> Value '
                                 || inner_index
                                 || ' = '
                                 || l_items (l_index) (inner_index));
         END LOOP;

         l_index := l_items.NEXT (l_index);
      END LOOP;
   END display_list;

   PROCEDURE show_variations
   IS
      PROCEDURE show_header (title_in IN VARCHAR2)
      IS
      BEGIN
         DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
         DBMS_OUTPUT.put_line (title_in);
         DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
      END show_header;
   BEGIN
      show_header ('Single Delimiter Lists');
      display_list ('a,b,c');
      display_list ('a;b;c', ';');
      display_list ('a,,b,c');
      display_list (',,b,c,,');

      show_header ('Nested Lists');
      display_list ('a,b,c,d|1,2,3|x,y,z', '|', ',');

      show_header ('Named, Nested Lists');
      display_list ('letters:a,b,c,d|numbers:1,2,3|names:steven,george'
                  , '|'
                  , ':'
                  , ','
                   );
   END;

   FUNCTION nested_eq (list1_in    IN items_tt
                     , list2_in    IN items_tt
                     , nulls_eq_in IN BOOLEAN
                      )
      RETURN BOOLEAN
   IS
      l_return   BOOLEAN := list1_in.COUNT = list2_in.COUNT;
      l_index    PLS_INTEGER := 1;
   BEGIN
      WHILE (l_return AND l_index IS NOT NULL)
      LOOP
         l_return := list1_in (l_index) = list2_in (l_index);
         l_index := list1_in.NEXT (l_index);
      END LOOP;

      RETURN l_return;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN FALSE;
   END nested_eq;
END;
/

Answer 5

使用 流水线表函数：

SQL> CREATE OR REPLACE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

SQL> CREATE OR REPLACE FUNCTION comma_to_table(
  2      p_list IN VARCHAR2)
  3    RETURN test_type PIPELINED
  4  AS
  5    l_string LONG := p_list || ',';
  6    l_comma_index PLS_INTEGER;
  7    l_index PLS_INTEGER := 1;
  8  BEGIN
  9    LOOP
 10      l_comma_index := INSTR(l_string, ',', l_index);
 11      EXIT
 12    WHEN l_comma_index = 0;
 13      PIPE ROW ( TRIM(SUBSTR(l_string, l_index, l_comma_index - l_index)));
 14      l_index := l_comma_index                                + 1;
 15    END LOOP;
 16  RETURN;
 17  END comma_to_table;
 18  /

Function created.

让我们看看输出：

SQL> SELECT *
  2  FROM TABLE(comma_to_table('12 3,456,,,,,abc,def'))
  3  /

COLUMN_VALUE
------------------------------------------------------------------------------
12 3
456




abc
def

8 rows selected.

SQL>

Answer 6

我只是想把理查德和迈克的答案写得更清楚。

方法 1：VARRAY USAGE

DECLARE
 TYPE v_array_type IS VARRAY (10) OF NUMBER;
 var v_array_type;
begin
 select to_number(column_value)  BULK COLLECT into var from  
 xmltable('1,2,3,4,5,6,7,8,9,10');
 for i in 1..var.count LOOP
  dbms_output.put_line(var(i));
 END LOOP;
end;

但在此方法中您必须声明数组大小。如果您想要更动态的方法，您可以使用迈克的方法。

方法二：

begin
 FOR obj IN (SELECT TO_NUMBER(column_value) as ID FROM 
 xmltable('1,2,3,4,5,6,7,8,9,10,11')) LOOP
  dbms_output.put_line(obj.ID);
 END LOOP;
end;

Answer 7

在 BBDD 上快速搜索后，我找到了一个名为 split 的函数：

create or replace function split
( 
p_list varchar2, 
p_del varchar2 := ','
) 
return split_tbl pipelined
is 
l_idx pls_integer; 
l_list varchar2(32767) := p_list;AA 
l_value varchar2(32767);
begin 
loop 
l_idx := instr(l_list,p_del); 
if l_idx > 0 then 
pipe row(substr(l_list,1,l_idx-1)); 
l_list := substr(l_list,l_idx+length(p_del));
else 
pipe row(l_list); 
exit; 
end if; 
end loop; 
return;
end split;

我不知道它是否有用，但我们找到了它 此处...

Answer 8

我正在寻找类似的解决方案，其中在逗号分隔的列表中包含多字节字符（连字符、空格、下划线）。所以 dbms_utility.comma_to_table 不适合我。

declare
  curr_val varchar2 (255 byte);
  input_str varchar2 (255 byte);
  remaining_str varchar2 (255 byte);
begin
  remaining_str := input_str || ',dummy';  -- this value won't output
  while (regexp_like (remaining_str, '.+,.+'))
  loop
    curr_val := substr (remaining_str, 1, instr (remaining_str, ',') - 1);
    remaining_str = substr (remaining_str, instr (remaining_str, ',') + 1);
    dbms_output.put_line (curr_val);
  end loop;
end;

这不是一个专家的答案，所以我希望有人能改进这个答案。

Answer 9

TYPE string_aa IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;

FUNCTION string_to_list(p_string_in IN VARCHAR2)
    RETURN string_aa
    IS
      TYPE ref_cursor IS ref cursor;
      l_cur    ref_cursor;
      l_strlist string_aa;
      l_x      PLS_INTEGER;
    BEGIN      
      IF p_string_in IS NOT NULL THEN 
         OPEN l_cur FOR 
            SELECT regexp_substr(p_string_in,'[^,]+', 1, level) FROM dual
            CONNECT BY regexp_substr(p_string_in, '[^,]+', 1, level) IS NOT NULL;      
         l_x := 1; 
         LOOP
           FETCH l_cur INTO l_strlist(l_x);
           EXIT WHEN l_cur%notfound;
           -- excludes NULL items  e.g.   1,2,,,,5,6,7
           l_x := l_x + 1;
         END LOOP;
      END IF;   
      RETURN l_strlist;
   END string_to_list;

Answer 10

你可以使用oracle方法

 declare
  myarray apex_application_global.vc_arr2;
 begin
  myarray = apex_util.string_to_table('1,2,3', ',');//you can use any symbols instead of ',' for split

  for i in 1..myarray.COUNT
    loop
      dbms_output.put_line(myarray(i));
    end  loop;

Answer 11

另一种可能性是：

create or replace FUNCTION getNth (
  input varchar2,
  nth number
) RETURN varchar2 AS
  nthVal varchar2(80);
BEGIN
  with candidates (s,e,n) as (
      select 1, instr(input,',',1), 1 from dual
      union all
      select e+1, instr(input,',',e+1), n+1
        from candidates where e > 0)
  select substr(input,s,case when e > 0 then e-s else length(input) end) 
    into nthVal
    from candidates where n=nth;
  return nthVal;
END getNth;

运行起来有点太昂贵，因为每次调用者请求其中的一项时，它都会计算完整的分割......

Answer 12

Stewart Ashton 在这个链接中提出的解决方案非常方便。
他消除了值列表必须是整数的需要，因此您可以使用字符串列表。

在WITH 子句中，他用单引号将值括起来，然后将其转换为具有单个VARCHAR2 类型列的表。
https://stewashton.wordpress.com

with data as (
  select '"'||replace(:txt, ',', '","')||'"' str from dual
)
select xmlcast(column_value as varchar2(4000)) subs
from data, xmltable(str);

Answer 13

declare
seprator varchar2(1):=',';
dosweeklist varchar2(4000):='a,b,c';
begin
for i in (SELECT  SUBSTR(dosweeklist,
                         case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end,
                         NVL(NULLIF(INSTR(dosweeklist,seprator,1,LEVEL),0),length(dosweeklist)+1) - case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end) dat 
          FROM dual
          CONNECT BY LEVEL <= LENGTH(dosweeklist) - LENGTH(REPLACE(dosweeklist,seprator,'')) +1)
loop
dbms_output.put_line(i.dat);
end loop;
end;
/

因此，仅在 for 循环中选择查询可以通过将 dosweeklist 替换为分隔字符串并将分隔符替换为分隔字符来实现此目的。

让我们看看输出

a

b

c

Answer 14

declare 
    v_str varchar2(100) := '1,2,3,4,6,7,8,9,0,';
    v_str1 varchar2(100); 
    v_comma_pos number := 0;    
    v_start_pos number := 1;
begin             
    loop        
    v_comma_pos := instr(v_str,',',v_start_pos);   
    v_str1 := substr(v_str,v_start_pos,(v_comma_pos - v_start_pos)); 
    dbms_output.put_line(v_str1);     
    if  v_comma_pos = 0 then     
    v_str1 := substr(v_str,v_start_pos);  
    dbms_output.put_line(v_str1);    
    exit;
    end if;    
    v_start_pos := v_comma_pos + 1;  
    if  v_comma_pos = 0 then    
    exit;
    end if;       
    end loop; 
end;

Answer 15

您可以使用替换功能轻松替换逗号。
要执行此操作 -

SQL Server (Transact-SQL) 中 REPLACE 函数的语法为：
REPLACE( string, string_to_replace, replacement_string )

参数或参数

string : 源字符串，其中的字符序列将被另一组字符替换字符。
string_to_replace： 将在 string1 中搜索的字符串。
replacement_string：替换字符串。所有出现的 string_to_replace 都将被替换为 string1 中的 replacement_string。
注意：

REPLACE 函数执行的替换不是
区分大小写。所以所有出现的 string_to_replace 都将是
替换为 replacement_string，无论大小写
string_to_replace 或 replacement_string

例如：
从 DUAL 中选择 REPLACE('Kapil,raj,chouhan', ',' , ' ');
结果：Kapil raj chouhan

SELECT REPLACE('I Live In India', ' ' , '-') from DUAL;
结果：I-Live-In-India

SELECT REPLACE('facebook.com', 'face' , 'friends') from DUAL;
结果：friendsbook.com

我希望它对您有用。

Answer 16

这是另一个更简单的选择

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

Answer 17

Oracle提供了内置函数DBMS_UTILITY.COMMA_TO_TABLE。

不幸的是，这个不适用于数字：

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

但是通过一个小技巧在元素前面加上“x”，它就可以工作：

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.

问候，
抢。