File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Clob in Oracle 10g Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Clob in Oracle 10g" Watch "Clob in Oracle 10g" New topic
Author

Clob in Oracle 10g

kv ruby
Ranch Hand

Joined: Jul 23, 2009
Posts: 38
Hi,

We are using both SQLServer and Oracle DataBase in our project. My issue is with SQL Server its easy to handle Clobs using

PreparedStatement pstmt;
pstmt.setString(1, clobData.getData());

i was wondering if there is any such way to handle clobs in Oracle,currently we are using

OraclePreparedStatement preparedStatement = (OraclePreparedStatement) pstmt;
preparedStatement.setStringForClob( 1, clobData.getData() );

1. i read it somwhere this way of handling clob is less efficient for large data (larger than 32765 bytes ) is that true ?
2. Would it be possible to handle Clobs in Oracle without using oracle specific classes?(like OraclePreparedStatement )?

Any help would be greatly appreciated am using oracle 10g.


Regards, ruby kv
rubyshiv.blogspot.com
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3435
    
  47

What happens if you try to use the first way of setting the CLOB with Oracle? (I have only ever used Oracle specific way of handling LOBs, as I don't have to support multiple databases in our project, so I honestly don't know.) I would expect there is a database-agnostic way of writing LOBs to Oracle, though it might be slower than some Oracle specific approaches.

The most efficient approach to set LOB in Oracle would be to insert/update a row so that it contains an empty LOB (using the the empty_clob() or empty_blob() SQL functions), and then fill up the LOB using the new lob handle. Other approaches usually cause the LOB to be created in temp area first and then copied to the table's LOB segment. For large OBs, that might be a concern, but for smaller ones probably not so much.

Details can be found in Oracle's documentation. Note that while this doc is the 10g version, you can use the 11g version JDBC driver with 10g database. There might be some enhancements regarding LOB manipulations available, if interested, look up the 11g version of the docs.
kv ruby
Ranch Hand

Joined: Jul 23, 2009
Posts: 38
Hi,

Though I could not find any harm in using Oracle specific classes there was a suggestion by few people in team to use a more general approach as we support both DataBases.

I could not find anything specific regarding that in the oracle documents too.
kv ruby
Ranch Hand

Joined: Jul 23, 2009
Posts: 38
"The most efficient approach to set LOB in Oracle would be to insert/update a row so that it contains an empty LOB (using the the empty_clob() or empty_blob() SQL functions), and then fill up the LOB using the new lob handle. Other approaches usually cause the LOB to be created in temp area first and then copied to the table's LOB segment. For large OBs, that might be a concern, but for smaller ones probably not so much."


did you mean using streaming is a better option?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3435
    
  47

One common approach to write a LOB is to create the LOB (somehow) and then use INSERT/UPDATE statement to set that LOB to a column in certain row. In Oracle, this approach causes the LOB to be created in temporary segment and then copied to the table's LOB segment, which carries the overhead of copying (for small LOBs, that would probably be unnoticeable). Oracle provides a way to create the LOB directly in its final place and then populate the LOB. This should be independent on the way you use to write the LOB data - streams, setString and so on.

So the most efficient way I mentioned earlier is the way that does not incur the overhead of copying the temporary LOB. I don't know at all whether the way used to populate the LOB (streams, setString etc) has any implicit effect on performance.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Clob in Oracle 10g
 
Similar Threads
Oracle Clob error on shared Sun One connection pool
hanging while inserting CLOB
Oracle JDBC driver does not support Datatypes defined in java.sql?
How to store & retrieve a video file from database?
how to create BLOB and CLOB data