使用to_char(wm_concat(item))的时候报ORA-64203错误

使用to_char(wm_concat(item))的时候报ORA-64203错误
出错原因：由于concat的item项目太多，导致合成后的字符串长度超出数据库char字段的缓冲区限制

通过DBMS_LOB.SUBSTR把wm_concat(item)分成10块，取到后再拼接起来

DBMS_LOB.SUBSTR(wm_concat(item),1000,1),
DBMS_LOB.SUBSTR(wm_concat(item),1000,1001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,2001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,3001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,4001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,5001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,6001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,7001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,8001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,9001),
DBMS_LOB.SUBSTR(wm_concat(item),1000,10001)

或者直接在oracle中用||连接起来，直接返回

DBMS_LOB.SUBSTR(wm_concat(item),1000,1) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,1001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,2001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,3001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,4001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,5001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,6001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,7001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,8001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,9001) ||
DBMS_LOB.SUBSTR(wm_concat(item),1000,10001)

参考oracle官网[url]http://download.oracle.com/docs/cd/E11882_01/server.112/e17766/e60000.htm[/url]：

ORA-64203: Destination buffer too small to hold CLOB data after character set conversion.
    Cause: An attempt was made to convert CLOB to CHAR, where the LOB size was bigger than the buffer limit for CHAR types or the CHAR buffer was not big enough to hold all data after character set conversion.
    Action: Do one of the following:

    * Make the LOB smaller before performing the conversion. for example, by using SUBSTR on CLOB

    * Use DBMS_LOB.SUBSTR to convert CLOB to CHAR.

DBMS_LOB.SUBSTR函数参考：
This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.

lob_loc：Locator for the LOB to be read.
amount：Number of bytes (for BLOBs) or characters (for CLOBs) to be read.
offset：Offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1).

[url]http://www.cs.umbc.edu/portal/help/oracle8/server.815/a68001/dbms_lob.htm#1009072[/url]