This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC 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


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reading Oracle CLOB by value" Watch "Reading Oracle CLOB by value" New topic
Author

Reading Oracle CLOB by value

Rajasekhar Devi Reddy
Greenhorn

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
Raj.
 
 
subject: Reading Oracle CLOB by value