It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Reading Oracle CLOB by value Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Reading Oracle CLOB by value" Watch "Reading Oracle CLOB by value" New topic

Reading Oracle CLOB by value

Rajasekhar Devi Reddy

Joined: Dec 21, 2007
Posts: 29
Hi all,

I have a tabla with a CLOB column in it and I need to be able to retrieve this clob value, along with others, using JDBC and create an XML. I did a performance test with this and found that just to read through a result set of 900 rows with one CLOB column in each, is taking 7 seconds.

My query goes like this

But if I change the query to

(** I know for this set of where clauses the size of the clob wont be too large that it can't fit in CHAR).

the time is down to one second or less. So the colb actually returns a locater and then JDBC will read the actual data when I call read on that column, resulting in 900 read operations taking too much time. Having the data converted to CHAR right in the SQL gives me great performance benefits. But I can not do it all the time as in some cases the value might not fit in CHAR and Oracle throws an exception.

Is there a way that I can read the actual data instead of retrieving a pointer to the CLOB data? Or is there any way of reading these CLOBS faster? I tried calling just rs.getString() (works with 10g & above) and also tried with and read a char stream. Both take some 6 to 7 seconds to loop through 900 records.

I would be thankful if some one who had seen this before cold suggest a better way or point me to right resources.

Thank you
I agree. Here's the link:
subject: Reading Oracle CLOB by value
It's not a secret anymore!