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.

Show multiple rows of a SQL query in a single row (comma separated)

Suppose If you have a SQL query which returns multiple rows when you join 2 tables, as the second table has multiple records for the same joining column of the first table.

In this case you can use the inbuilt XMLAGG function available in SQL.

Table 1 -> TXN_PROC_OPN

Table 2 -> TXN_PROC_REMARK_M

SQL Query ->

SELECT 
           TPO.WORKITEMID, 
           TRIM(',' FROM XMLAGG(XMLELEMENT(E,PRM.REMARKS || ',' ) ORDER BY                                                              PRM.WORKITEMID).EXTRACT('//text()')) AS MAKER_REMARKS
FROM
           TXN_PROC_OPN TPO, TXN_PROC_REMARK_M PRM
WHERE
           TPO.WORKITEMID = PRM.WORKITEMID
GROUP BY
           TPO.WORKITEMID


Output->

Jasper Reports Not opening on all Environments

Sometimes your Jasper Reports work on one environment and don't work on others.

We get the following error -NoClassDefFoundError:net/sf/jasperreports/engine/util/JRStyledTextParser

If you look at the logs closely, you will get the root exception which gets thrown before the above exception.
Root Exception - java.lang.NoClassDefFoundError: sun/awt/X11GraphicsEnvironment

To solve this issue we have to set the following Argument at server start up ->
java.awt.headless=true

Java uses the underlying operating system to generate images. If you are using Unix and do not start the portal in a X Windows session, then Java will not know how to generate images and you'll get lots of nasty exceptions. Setting this property to true will fix that. Sometimes this property cannot be set dynamically when the server starts and you'll need to edit your start script to include this as a system property.

Steps ->

1) Login to weblogic console and click on servers.

2) Click on the "server" that you have configured.

3) Now click on "Server Start" Tab.

4) Add the following line (-Djava.awt.headless=true) in Arguments section as shown below.