• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Reading Oracle CLOB by value

Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
mooooooo ..... tiny ad ....
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic