How to read CLOB column in Oracle DataBase from Java

Question

I have a table in Database where datatype of a column(STATUS) is CLOB.I need to read that STATUS

create table STATUS_TABLE
(
STATE_ID      number(20,0),
STATUS   clob
)

I am trying to read CLOB column as below

String getStatus = "SELECT STATUS FROM STATUS_TABLE WHERE STATE_ID="+id;
Query statusResults = session.createSQLQuery(getStatus);
List statusRes = statusResults.list();
if ((statusRes != null) && (statusRes.size() > 0)) {
        oracle.sql.CLOB clobValue = (oracle.sql.CLOB) statusRes.get(0);
        status = clobValue.getSubString(1, (int) clobValue.length());
        log.info("Status->:" + status.toString());
}

And getting the error as

 java.lang.ClassCastException: $Proxy194 cannot be cast to oracle.sql.CLOB

How can i read clob data from DB and convert to String ?

what data you are trying to store ?? why are you using CLOB ? — Ravi
– Ravi, Commented Jul 14, 2017 at 6:22
I would think using a PreparedStatement -> resultSet and then use docs.oracle.com/javase/7/docs/api/java/sql/… would be better — Scary Wombat
– Scary Wombat, Commented Jul 14, 2017 at 6:26
Also your Table says STATUS but your SQL says SELECT STATUS_DESCRIPTION — Scary Wombat
– Scary Wombat, Commented Jul 14, 2017 at 6:27

Tim Biegeleisen · Accepted Answer · 2017-07-14 06:55:03Z

Here is the corrected version, and an explanation appears below the code:

Query query = session.createSQLQuery("SELECT STATUS FROM STATUS_TABLE WHERE STATE_ID = :param1");
query.setInt("param1", id);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List statusRes = query.list();
if (statusRes != null) {
    for (Object object : statusRes) {
        Map row = (Map)object;
        java.sql.Clob clobValue = (java.sql.Clob) row.get("STATUS");
        status = clobValue.getSubString(1, (int) clobValue.length());
        log.info("Status->:" + status.toString());
    }
}

Problems I saw with your code:

You were building a raw query string using concatenation. This leaves you vulnerable to SQL injection and other bad things.
For whatever reason you were trying to cast the CLOB to oracle.sql.CLOB. AFAIK JDBC will return a java.sql.Clob
You are performing a native Hibernate query, which will return a list result set whose type is not known at compile time. Therefore, each element in the list represents one record.

Collectives™ on Stack Overflow

How to read CLOB column in Oracle DataBase from Java

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related