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.

Solution for larger numbers which get read in exponential format from excel.

When you try to read excel in JAVA and come across cells which have Numbers greater than 12 digits, it will automatically get converted in Exponential format.

Example: 104009000084 gets converted to 1.04009000084E11

Now even if you change the cell format from Number to Text, it still considers it as Number and converts it to exponential format while reading the file in JAVA.

Solution 1:
Write sign ' before the number in excel sheet.

Solution 2:
Delete the value from excel , change the format from Number to Text and then rewrite the value back to excel.

POI API for writing data from excel file to a text file.

Many times you will come across situations, wherein you will need to read data from excel files and do some processing.

POI API comes to the rescue here :)

Download the latest POI jar file and add it to your project library.

import java.io.File;
import java.io.FileInputStream;
import java.io.PrintWriter;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ConvertFile 
{
   public static void main(String[] args) 
   {
      System.out.println("Start of Program");
      try
      {
FileInputStream file = new FileInputStream(new File("D:\\Test_Data_Automation.xls"));

//Get the workbook instance for XLS file 
HSSFWorkbook workbook = new HSSFWorkbook(file);

//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(2);

//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();

String fileName = "";
PrintWriter writer = null;

        while(rowIterator.hasNext())
         {
            Row row = rowIterator.next();
       
            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
             
            try{
                 writer = new PrintWriter("D:\\Output_Data.txt", "UTF-8");
            }catch(Exception ex){ex.printStackTrace();}
                          
            while(cellIterator.hasNext()) 
            {
               Cell cell = cellIterator.next();
                       
            if(cell.getRowIndex() > 3)      //Start reading from the 3rd row.
            {
               switch(cell.getCellType()) 
               {
                  case Cell.CELL_TYPE_BOOLEAN:
                  System.out.print(cell.getBooleanCellValue() + "\t\t");
                  writer.println(String.valueOf(cell.getBooleanCellValue()));
                  break;
                  
                   case Cell.CELL_TYPE_NUMERIC:
                  System.out.print(cell.getNumericCellValue() + "\t\t");
                  writer.println(String.valueOf(cell.getNumericCellValue()));
                  break;
                   
                   case Cell.CELL_TYPE_STRING:
                  System.out.print(cell.getStringCellValue() + "\t\t");
                  writer.println(String.valueOf(cell.getStringCellValue()));
                  break;
                   
                   case Cell.CELL_TYPE_FORMULA:
                  cellValue=String.valueOf(cell.getStringCellValue());
                  break;
                   
                   case Cell.CELL_TYPE_ERROR:
                  cellValue=String.valueOf(cell.getStringCellValue());
                  break;
                  }
            }
             }
             
             try{
             System.out.println("Close the file");
                 writer.close();
             }catch(Exception ex){ex.printStackTrace();}
         }

         System.out.println("End of Program");

     }
     catch(Exception ex) {ex.printStackTrace();}
   }
}   //End of Main


Some Important syntax->
1) To read any row apart from the current row, use the following.
     Row nextRow = sheet.getRow(row.getRowNum() + 1);
     Row prevRow = sheet.getRow(row.getRowNum() - 1);
     Row next2Row = sheet.getRow(row.getRowNum() + 2);
     Row prev2Row = sheet.getRow(row.getRowNum() - 2);
     etc.

2) To read any column in any particular row, use the following.
     row.getCell(1);
     row.getCell(7);
     row.getCell(11);
     etc.