Hi all,
I’ve got problems putting long Strings (>32768 chars) into a CLOB field in an Oracle DB. I’ve investigated a little bit and found out that this comes from JDBC and the way how Hibernate uses it.
If your SQL is “INSERT INTO … VALUES (?)”, there is no problem using setString on the PreparedStatement, even if the String is very long.
But if your SQL is “INSERT INTO … SELECT ? FROM DUAL” AND your String is long AND you use setString, you will earn a ORA-01461. This also holds true if you try a cast like in “INSERT INTO … SELECT to_clob(?) FROM DUAL”.
Regarding JDBC, you can fix it if you use setCharacterStream on the PreparedStatement instead of setString.
But how is this related to Hibernate?
Well, if you use the upsert method of a StatelessSession, Hibernate will compose a MERGE statement using exactly this SELECT ? FROM DUAL idiom.
Therefore currently it is simply impossible to use upsert with an Entity which has a field which maps to a Oracle CLOB column having Strings longer than 32768.
Currently I worked around this by creating a JdbcType and annotating it in the affected Entity, but of course it would be nice if Hibernate would support this out of the box.
Would it be worth the effort creating a bug report for this? Or is it a feature request? The only change would be to use setCharacterStream instead of setString in the situation of long Strings with CLOBs.
Marcus.