2006-12-19

Oracle 8 CLOB problem with Thin driver solved

Project: workflow system for major telecom company
Technologies: Java 5, Ajax(Echo2), Weblogic, jBpm, Hibernate
The problem: we have to connect to an Oracle 8.1.7 database and we have been experiencing all kind of weird stuff depending on the driver we were using.

Dispite Oracle states that all current drivers are compatible with (Oracle says: can talk to:) RDBMS 8.1.7, we had to face several incompatibility issues.

First we tried to use the latest 10g thin drivers. Everything went well until we tried to use a CLOB field to store special Hungarian text (árvíztűrőtükörfúrógép). We were unable to retrieve anything better that ?rv?zt?r?t?k?rf?r?g?p using JDBC, although TOAD showed the inserted values perfectly.

Then we switched to version 8 thin driver which proved to be the only Oracle JDBC thin driver capable of retrieving Hungarian chars from CLOB fields of a 8.1.7 database.

Things got worse when the text size increased, suddenly we started to receive this:
java.sql.SQLException: operation not allowed: streams type cannot be used in batching

It seems that the old JDBC driver does not support batch updates of Hibernate, therefore we disabled the batch update feature: hibernate.jdbc.batch_size=0

It didn't help. At the moment of DB connection closing, we got a deadlock... Awesome... What next?

Final attepmt: we switched to the OCI driver and it works well.

Conclusion: if you need to use CLOB fields with Oracle 8, your recommended choice is the OCI driver.

No comments: