how to Retrive the CLOB value from Oracle using java

Question

SELECT DESCRIPTION,DETAILED_DESCRIPTION,PRIORITY,RISK_LEVE FROM Table_Name

The DETAILED_DESCRIPTION column is having value in CLOB

Below is the code is used to fetch the data: But i am getting the error "Error: Read error" while reading the field "DETAILED_DESCRIPTION"

Statement statement;

ResultSet resultSet;

oracleCon.setAutoCommit(false);

statement = oracleCon.createStatement();

String chdet[] = new String[8];
String query="SELECT DESCRIPTION,DETAILED_DESCRIPTION,PRIORITY,RISK_LEVEL FROM Table_Name"; 

                    resultSet = statement.executeQuery(query);
                    ArrayList<String> record=new ArrayList<String>();               

                    while (resultSet.next())
                    {
                    record.add(resultSet.getString("DESCRIPTION"));                 
                    record.add(resultSet.getString("DETAILED_DESCRIPTION"));
                    record.add(resultSet.getString("PRIORITY"));
                    record.add(resultSet.getString("RISK_LEVEL"));              
                    }                   
                    if(record.size()>0)             
                    {
                        chdet[0] = record.get(0);
                        chdet[1] = record.get(1);
                        chdet[2] = record.get(2);
                        chdet[3] = record.get(3);
                        break;                          
                    }                               
                }
            return chdet;   

Slabber

take a look at ResultSet.getClob here docs.oracle.com/javase/1.4.2/docs/api/java/sql/… – Bernardo

What is your driver version? It's usually inside the MANIFEST.MF in the jar file (the number in the filename is not the driver version). For an up-to-date driver (11.x) using getString() on a CLOB should work just fine. – Lean

if you stil couldnt solve it, please take a look at this answer https://mcmap.net/q/670772/-how-to-persist-a-jpa-field-as-a-clob – Diaz

Answer 1

After retrieving your data, you can use the getClob () method to return your Clob. Then you needs to open the Clob's stream to read the data (Mayb be char or binary data).

If the clob is known to be a plain string, you maybe also wish to use

clob.getSubString(1, (int) clob.length());

So try this

Clob clob = resultSet.getClob("DETAILED_DESCRIPTION")
record.add(clob.getSubString(1, (int) clob.length());

see http://www.java2s.com/Code/JavaAPI/java.sql/ResultSetgetClobintcolumnIndex.htm

Fabe

i have tried to use the getClob() method as record.add(resultSet.getString("DETAILED_DESCRIPTION")); But i ma seeing the error "The method add(String) in the type ArrayList<String> is not applicable for the arguments (Clob)" – Slabber

This works in most cases, but it doesn't work if the number of characters in the CLOB is larger than Integer.MAX_VALUE. In that case, you won't be able to cast clob.length() to int and you'll get an exception. If you want to make sure that it works all the time, you'll need to iterate over it and increase position (which is a long value). – Asberry

Answer 2

This might help you,

    // Select LOB locator into standard result set.
ResultSet rs =
   stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{
   // Get LOB locators into Java wrapper classes.
   java.sql.Blob blob = rs.getBlob(1);
   java.sql.Clob clob = rs.getClob(2);

}

Refer to below link for more details, http://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/oralob2.htm

Aubree

ResultSet has getClob () method, why not use it? – Fabe

i have tried to use the getClob() method as record.add(resultSet.getString("DETAILED_DESCRIPTION")); But i ma seeing the error "The method add(String) in the type ArrayList<String> is not applicable for the arguments (Clob)" – Slabber

As error says, you cannot add clob object to ArrayList<String>. Pls make genre of arraylist to Clob ArrayList<Clob>. – Aubree