dbms_utility.table_to_comma使用的注意点

最新推荐文章于 2023-08-11 16:21:43 发布

daimin1983

最新推荐文章于 2023-08-11 16:21:43 发布

阅读量2.4k

点赞数

分类专栏： Oracle 文章标签： integer function qq 测试 table 存储

本文链接：https://blog.youkuaiyun.com/daimin1983/article/details/3428804

版权

Oracle 专栏收录该内容

61 篇文章

订阅专栏

本文介绍在Oracle PL/SQL中使用dbms_utility.table_to_comma函数时需要注意的细节，特别是关于uncl_array数组元素长度限制的问题，并提供了一种替代方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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