dbms_utility.table_to_comma使用的注意点

dbms_utility.table_to_comma使用的注意点

有下面的一个存储过程：
CREATE OR REPLACE FUNCTION Mytable_to_comma22(v_pkid number
                                           ) RETURN VARCHAR2 AS
  v_invoicecodestring varchar2(4000);
  qq1 dbms_utility.uncl_array;  --此处定义一个dbms_utility.uncl_array数组，为使用dbms_utility.table_to_comma函数做准备工作
  l_tablen number;

  begin
  select t2.origincode bulk collect into qq1
   from TSALESORDERINVOICE d, tsalesinvoice t2
    where d.orderid =v_pkid
      and d.invoiceid = t2.pkid
      and d.valid = 'T'
      and t2.valid = 'T';
  /*fetch mycursor bulk collect into qq1 ，此处使用dbms_utility.table_to_comma函数*/
  dbms_utility.table_to_comma(qq1, l_tablen, v_invoicecodestring);
  RETURN  v_invoicecodestring;
end Mytable_to_comma22;

测试时，输入一个值：1004472556，执行后报下面的错误：
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at "USER_DING.MYTABLE_TO_COMMA2", line 8
View program sources of error stack?

然后我查找了下原因,通过此v_PKID的值传进去的，单独查询
SQL> select t2.origincode, length(t2.origincode) as len
  2    from TSALESORDERINVOICE d, tsalesinvoice t2
  3   where d.orderid = 1004472556
  4     and d.invoiceid = t2.pkid
  5     and d.valid = 'T'
  6     and t2.valid = 'T';
ORIGINCODE                                                                              LEN
-------------------------------------------------------------------------------- ----------
17738064,17738065,17738066,17738067,17738068,17738069,17738070,17738071,17738072        341

发现这个origincode的长度很长，达到341，此时想知道qq1 dbms_utility.uncl_array;所定义的数组中的每个元素的大小是多少？

查到官方文档，如下定义：
uncl_array
TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

可以说明，dbms_utility.uncl_array这个是一个数组，并且数组中每个元素的最大长度被限制为227，也就是数组中元素的最长是227，
超过这个值就是报错

至此知道了错误的原因，然后将上面的存储过程的写法换种写法：
CREATE OR REPLACE FUNCTION Mytable_to_comma2(v_pkid number
                                           ) RETURN VARCHAR2 AS
  v_invoicecodestring varchar2(4000);
  v_bin   varchar2(1000);
  v_char  varchar2(2);
  mycursor sys_refcursor;
  begin
  v_char:= ',';
  open mycursor for
  select t2.origincode
   from TSALESORDERINVOICE d, tsalesinvoice t2
    where d.orderid =v_pkid
      and d.invoiceid = t2.pkid
      and d.valid = 'T'
      and t2.valid = 'T';
  loop
  fetch mycursor into v_bin;
  EXIT WHEN mycursor%NOTFOUND;
  v_invoicecodestring:=v_invoicecodestring||v_bin||v_char;
  END LOOP;
  CLOSE mycursor;
  RETURN  v_invoicecodestring;
  end Mytable_to_comma2;

换种写法，主要是自己定义每个元素的长度为v_bin   varchar2(1000)，先将查询出来的值输入到v_bin的临时参数中，然后再自己手动串起来的方法；

下面重新采用新的写法测试下：
测试时，输入一个值：1004472556，此时不报错了，并且输入结果如下：
result String 17738064,17738065,17738066,17738067,17738068,17738069,17738070,17738071,17738072,17738073,17738074,17738075,17738076,17738077,17738078,17738079,17738080,17738081,17738082,17738083,17738084,17738085,17738086,17738087,17738088,17738089,17738090,17738091,17738092,17738093,17738094,17738095,17738096,17738097,17738098,17738099,17738100,17738101,
v_pkid Float 1004472556

结论：使用dbms_utility.table_to_comma ，要注意在使用dbms_utility.uncl_array时注意输入每个元素的字符串的最长长度，因为，dbms_utility.uncl_array的定义为uncl_array TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER，数组中每个元素的最大长度被限制为227，也就是数组中元素的最长是227，超过这个值就是报错ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind