ORACLE string buffer is too small Solution

ORACLE string buffer is too small solution today we encounter a problem where the string buffer is too small because we use the ORACLE aggregate concatenation function WM_CONCAT. The error message is as follows:

1 ### Error querying database. cause: java. SQL. SQLException: ORA-06502: PL/SQL: Number or value error: String Buffer too small 2ORA-06512: In "WMSYS. WM_CONCAT_IMPL ", the SQL exception that line 30 actually reports is complicated, and it is extracted probably like this: 1 SELECT WMSYS. WM_CONCAT (distinct compname) FROM2 (SELECT * FROM3 (select newscode, compcode from T_NEWS_COMPANY_BD) A4INNER JOIN TQ_COMP_INFO C5ON. COMPCODE = C. COMPCODE) GROUP BY NEWSCODE

 

Because the number of compnames in some groups is too large After grouping by NEWSCODE, it is more than 4000 characters After splicing. ORACLE is too stupid and will not directly add ellipsis to ignore the subsequent results, instead, an error is returned. I also searched some solutions on the internet, either user-defined aggregate functions, CLOB field processing, or nested layers to filter out too many records, which is too complicated, the problem cannot be solved quickly. After thinking for a long time, I finally thought of using partition by to solve this problem. The resolved SQL statement is as follows:

1SELECT WMSYS.WM_CONCAT(DISTINCT CASE WHEN RANK < 100 THEN COMPNAME END) FROM2(SELECT * FROM3(SELECT NEWSCODE,COMPCODE,RANK() OVER(PARTITION BY NEWSCODE ORDER BY COMPCODE) RANK FROM T_NEWS_COMPANY_BD) A4INNER JOIN TQ_COMP_INFO C5ON A.COMPCODE = C.COMPCODE) GROUP BY NEWSCODE

 

This method requires a very small amount of SQL code to be modified. If you do not care about the slight performance loss caused BY the omitted fields and partition by, I personally think it is the best solution.