[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4635) Oracle ORA-24816 inserting and updating data for entities containg LOB attributes

[Taro App (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-4635?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45535#comment-45535 ] 

Taro App commented on HHH-4635:
-------------------------------

I have the same issue. I workaround the issue by using hibernate dynamic update functionality and asking users to update long text data fields one by one. That way, long CLOB and long VARCHAR2 columns are never updated at once. I'm lucky that the users do not complain so much.

Just FYI, Oracle handles data potentially > 4000 bytes differently from data known to be <= 4000 bytes. The former is handled like LONG/CLOB, the latter is handled like VARCHAR2. Depending of database charset and client charset settings, number of characters safe to be <= 4000 bytes is different. For my Japanese environment with Oracle XE 11i (AL32UTF8 charset,) 1 character can be 6 bytes in AL32UTF8, so only 666 characters are safe. (666 * 6 = 3996, 667 * 6 = 4002.)

So for me,
ok : insert into (clobfield, varcharfield, id) values (str666, str666, 123);
ok : insert into (clobfield, varcharfield, id) values (str667, str666, 123);
ok : insert into (clobfield, varcharfield, id) values (str666, str667, 123);
err: insert into (clobfield, varcharfield, id) values (str667, str667, 123);
ok : insert into (varcharfield, clobfield, id) values (str666, str666, 123);
ok : insert into (varcharfield, clobfield, id) values (str667, str666, 123);
ok : insert into (varcharfield, clobfield, id) values (str666, str667, 123);
ok : insert into (varcharfield, clobfield, id) values (str667, str667, 123);
For other environments, maximum safe string length can be 1333 (1333 * 3 = 3999) or 1000 (1000 * 4 = 4000.)

in any case, if long/clob/blob columns appear at last in insert/update statements, it should work fine.

This issue is around from a long time ago (2007!): https://forum.hibernate.org/viewtopic.php?p=2396711

> Oracle ORA-24816 inserting and updating data for entities containg LOB attributes
> ---------------------------------------------------------------------------------
>
>                 Key: HHH-4635
>                 URL: https://hibernate.onjira.com/browse/HHH-4635
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.3.1
>            Reporter: Clay Atkins
>            Assignee: Strong Liu
>            Priority: Critical
>         Attachments: oracleLobTest2.zip
>
>
> Inserting or updating an entity with LOB results in the following error from oracle:
> {noformat}
> Caused by: java.sql.SQLException: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column
> {noformat}
> Fairly confident that the ordering of the LOB attributes is not putting them at the end of the insert or the update. Oracle requires all LOB columns to be the last in the statement.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira