Buffer too small for CLOB to CHAR or BLOB to RAW conversion in Oracle.

Sometimes when we try to fetch some column from database, which has large number of character data, we get the following error->

SQL->

SELECT
            WORKITEMID , EXCEPTION_DESC
FROM
       (SELECT
                 TPO.WORKITEMID,
                 TRIM(',' FROM XMLAGG(XMLELEMENT(E,ED.EXCEPTION_DESC || ',' ) ORDER BY                                                         ED.WORKITEMID).EXTRACT('//text()')) AS EXCEPTION_DESC
       FROM
                 FILE_PROC_OPN FPO, TXN_PROC_OPN TPO, EXCEPTION_DETAILS ED
      WHERE
                 FPO.WORKITEMID=TPO.FILE_WORKITEM_ID
                AND TPO.WORKITEMID=ED.WORKITEMID
       GROUP BY
                TPO.WORKITEMID, FPO.SENDER_NAME, TPO.PRODUCT_ID, FPO.CUST_ID, FPO.CREATION_DATE,
                TPO.TXN_REF_NUM, FPO.FILE_REF_NUM, TPO.DR_ACCT_NO, TPO.DR_AGT_CLRING_SYS_CD,
                TPO.INSTR_CURRENCY, TPO.INSTR_AMOUNT, TPO.MAKER_DATE);

Error ->

The workaround for this is to use getClobVal() function in inner query and fetch it in outer query by using TO_CHAR

SELECT 
            WORKITEMID , TO_CHAR(EXCEPTION_DESC)
FROM
           (SELECT
                   TPO.WORKITEMID, 
                   TRIM(',' FROM XMLAGG(XMLELEMENT(E,ED.EXCEPTION_DESC || ',' ) ORDER BY                                                          ED.WORKITEMID).EXTRACT('//text()').getClobVal()) AS EXCEPTION_DESC   
           FROM
                   FILE_PROC_OPN FPO, TXN_PROC_OPN TPO, EXCEPTION_DETAILS ED
           WHERE
                    FPO.WORKITEMID=TPO.FILE_WORKITEM_ID
                    AND TPO.WORKITEMID=ED.WORKITEMID
           GROUP BY
                   TPO.WORKITEMID, FPO.SENDER_NAME, TPO.PRODUCT_ID, FPO.CUST_ID, FPO.CREATION_DATE, 
                   TPO.TXN_REF_NUM, FPO.FILE_REF_NUM, TPO.DR_ACCT_NO, TPO.DR_AGT_CLRING_SYS_CD, 
                   TPO.INSTR_CURRENCY, TPO.INSTR_AMOUNT, TPO.MAKER_DATE);


Even after this you may still get the following error ->

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5940, maximum: 4000)
22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"

*Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where the LOB size was bigger than the buffer limit for CHAR and RAW types.

Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are
reported in bytes.

*Action: Do one of the following

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

2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.



In this case you have to use DBMS_LOB.SUBSTR, as shown in below SQL.

SELECT 
            WORKITEMID , DBMS_LOB.SUBSTR(EXCEPTION_DESC,4000)
FROM
           (SELECT
                   TPO.WORKITEMID, 
                   TRIM(',' FROM XMLAGG(XMLELEMENT(E,ED.EXCEPTION_DESC || ',' ) ORDER BY                                                          ED.WORKITEMID).EXTRACT('//text()').getClobVal()) AS EXCEPTION_DESC   
           FROM
                   FILE_PROC_OPN FPO, TXN_PROC_OPN TPO, EXCEPTION_DETAILS ED
           WHERE
                    FPO.WORKITEMID=TPO.FILE_WORKITEM_ID
                    AND TPO.WORKITEMID=ED.WORKITEMID
           GROUP BY
                   TPO.WORKITEMID, FPO.SENDER_NAME, TPO.PRODUCT_ID, FPO.CUST_ID, FPO.CREATION_DATE, 
                   TPO.TXN_REF_NUM, FPO.FILE_REF_NUM, TPO.DR_ACCT_NO, TPO.DR_AGT_CLRING_SYS_CD, 
                   TPO.INSTR_CURRENCY, TPO.INSTR_AMOUNT, TPO.MAKER_DATE);


Here DBMS_LOB.SUBSTR(EXCEPTION_DESC,4000) means we are selection only the first 4000 characters.