ORA-06502:PL/SQL:数字或值错误：字符串缓冲区太小

ORA-06502:PL/SQL:数字或值错误：字符串缓冲区太小
ORA-06512:在"WMSYS.WM_CONCAT_IMPL",line 30

   记录一下，今天在合并订单号的时候出现了这个错误，想了很久没搞定，在网上搜了多种方法都没有解决（包括自定函数、用系统函数都不行），最后和公司的专业做数据库同事请教得以解决，如下：

 

  一开始用的：    

    SELECT a.ordercode, 
                    MAX(a.ordertime) as ordertime,
                    a.storename,
                    a.buyername,
                    a.orderstatus,
                    a.id,
                    a.ordertype AS OrderStatusType,
                    wmsys.wm_concat(d.name) AS B_NAME
              FROM  orderbase a
                    LEFT JOIN Orderdetail b ON a.id = b.orderid AND b.isdeleted = 0
                    LEFT JOIN productinfo c ON b.productid=c.id AND c.isdeleted=0
                    LEFT JOIN Categorybase d ON c.categoryid = d.id AND d.isdeleted = 0
                    LEFT JOIN Categorybrand e ON d.id = e.categoryid AND e.isdeleted = 0
                    LEFT JOIN brandbase f ON f.id=e.brandid AND f.isdeleted = 0
              WHERE a.IsDeleted=0
           GROUP BY a.id,
                    a.ordercode,
                    a.buyername,
                    a.storename,
                    a.orderstatus,
                    a.ordertype             

 

解决方案：

             SELECT a.ordercode, 
                    MAX(a.ordertime) as ordertime,
                    a.storename,
                    a.buyername,
                    a.orderstatus,
                    a.id,
                    a.ordertype AS OrderStatusType,
                    trim(both '','' from
                    sys.stragg(to_char(d.name) || nvl2(d.name, '','', ''''))) as B_NAME
              FROM  orderbase a
                    LEFT JOIN Orderdetail b ON a.id = b.orderid AND b.isdeleted = 0
                    LEFT JOIN productinfo c ON b.productid=c.id AND c.isdeleted=0
                    LEFT JOIN Categorybase d ON c.categoryid = d.id AND d.isdeleted = 0
                    LEFT JOIN Categorybrand e ON d.id = e.categoryid AND e.isdeleted = 0
                    LEFT JOIN brandbase f ON f.id=e.brandid AND f.isdeleted = 0
              WHERE a.IsDeleted=0
           GROUP BY a.id,
                    a.ordercode,
                    a.buyername,
                    a.storename,
                    a.orderstatus,
                    a.ordertype