Clob in Oracle 10g

[kv ruby]

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.

[Martin Vashko]

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]

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]

"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 Vashko]

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.