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;

take a look at ResultSet.getClob here http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html#getClob(java.lang.String) — Satya, Oct 21 '13 at 05:12
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. — , Oct 21 '13 at 06:03
if you stil couldnt solve it, please take a look at this answer https://stackoverflow.com/a/66838827/10499624 — Aramis NSR, Mar 28 '21 at 17:19

Scary Wombat · Accepted Answer · 2019-06-16T23:44:02.720

22

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

edited Jun 16 '19 at 23:44

answered Oct 21 '13 at 05:18

Scary Wombat

44,617
6
35
64

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 is not applicable for the arguments (Clob)" – user1882624 Oct 21 '13 at 06:13
1

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). – Fabian May 24 '23 at 08:10

Dark Knight · Answer 2 · 2013-10-21T05:22:36.973

0

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

edited Oct 21 '13 at 05:22

answered Oct 21 '13 at 05:16

Dark Knight

8,218
4
39
58

ResultSet has getClob () method, why not use it? – Scary Wombat Oct 21 '13 at 05:20
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 is not applicable for the arguments (Clob)" – user1882624 Oct 21 '13 at 06:16
As error says, you cannot add clob object to ArrayList. Pls make genre of arraylist to Clob ArrayList. – Dark Knight Oct 21 '13 at 06:33

how to Retrive the CLOB value from Oracle using java

2 Answers2

Linked