WM_CONCAT字符超过4000的处理办法 (ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小)

最新推荐文章于 2024-06-18 17:15:24 发布

原创最新推荐文章于 2024-06-18 17:15:24 发布 · 6.2k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #WM_CONCAT # PL/SQL: 数字或值错误 : 字符串缓冲区太小

Oracle相关同时被 2 个专栏收录

19 篇文章

订阅专栏

JAVA基础知识

11 篇文章

订阅专栏

本文介绍了当在Oracle中使用WM_CONCAT或自定义聚合函数进行字符串拼接时，如果结果超过4000字符限制的解决方法。提供了两种有效方案：一是创建并使用特定函数将结果集转换为CLOB格式；二是利用Oracle SQL内置的XMLAGG函数来实现大字符串的拼接。

部署运行你感兴趣的模型镜像

在进行使用WM_CONCAT或者自定义的聚合函数，进行拼串的时候，可能遇到拼串形成的结果集大于4000，这时候，系统会提示，超过系统限制。所以，在这个时候，最好的处理办法就是将结果集处理成CLOB格式，

下面共有两种处理方式：

1、使用函数

类型：

 create or replace type str2tblType as table of varchar2(4000)

函数：

 CREATE OR REPLACE FUNCTION tab2clob(p_str2tbltype str2tbltype,
                     p_delim       IN VARCHAR2 DEFAULT ',') RETURN CLOB IS

    l_result CLOB;
    BEGIN

    FOR cc IN (SELECT column_value
                   FROM TABLE(p_str2tbltype)
                 ORDER BY column_value) LOOP
       l_result := l_result || p_delim || cc.column_value;
     END LOOP;
    RETURN ltrim(l_result, p_delim);
   END;

测试:

初始化数据：

 BEGIN2   FOR idx IN 1 .. 10000 LOOP
     INSERT INTO ts1 (tm) VALUES (sys_guid());
  END LOOP;
 END;

测试的SQL语句：

 SELECT tab2clob(CAST(COLLECT(tm) AS str2tbltype)) attributes
   FROM ts1
  WHERE rownum < 1000

注意：

如下的SQL语句错误：由于类型不同

1 SELECT sys_util.tab2clob(CAST(COLLECT(deptno) AS str2tbltype))
2   FROM (SELECT DISTINCT deptno FROM emp)

会抛出如下的异常信息：

因为在str2tbltype中声明的是varchar2，但是现在deptno是数字，所以数据类型会发生不一致，所以，可以使用to_char见其进行转换，来避免上述的错误：

1 SELECT sys_util.tab2clob(CAST(COLLECT(to_char(deptno)) AS str2tbltype))
2   FROM (SELECT DISTINCT deptno FROM emp)

二：使用Oracle的SQL提供的处理XML的语句：XMLAGG()

SQL语句如下：

1 SELECT rtrim(xmlagg(xmlparse(content ename || ',' wellformed) ORDER BY ename)
2              .getclobval(),
3              ',') attributes,
4        deptno
5   FROM emp
6  GROUP BY deptno;

结果：

具体：

或者使用如下的语句，可以实现同样的功能：

参考网站：http://www.williamrobertson.net/documents/one-row.html

1  SELECT deptno,
2        trim(xmlagg(xmlelement(content, ename || ',' ) ORDER BY ename)
3              .extract('//text()').getclobval())
4               AS concatenated
5   FROM emp
6  GROUP BY deptno;

下面的语句，没有调用getClobVal（），聚合的结果集是字符串

1  SELECT deptno,
2        trim(xmlagg(xmlelement(content, ename || ',' ) ORDER BY ename)
3              .extract('//text()'))
4               AS concatenated
5   FROM emp
6  GROUP BY deptno;