[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2290) Using a composite-element in a set creates invalid SQL on Oracle if composite-element has a CLOB field

Using a composite-element in a set creates invalid SQL on Oracle if composite-element has a CLOB field ------------------------------------------------------------------------------------------------------ Key: HHH-2290 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2290 Project: Hibernate3 Type: Bug Versions: 3.2.1 Environment: Oracle, version: Oracle9i Enterprise Edition Release 9.2.0.7.0 Reporter: Frank Durden Attachments: test-case.jar Hi, With a mapping like the one below I get an "ORA-00932: inconsistent datatypes: expected - got CLOB" if I remove or modify a ChildElement: When Hibernate tries to remove the ChildElement it generates SQL like "delete from TEST_CHILDREN where PARENT_ID=? and NAME=? and VALUE=?". It is not possible in Oracle to use the equal sign with CLOB fields. Test case attached. A workaround is to use a stored procedure to delete the ChildElement using a "<sql-update callable="true">", but this works only as long as the CLOB field is <32k. With values bigger than 32k you get an "ORA-01460: unimplemented or unreasonable conversion requested", at least with an OCI driver. Anyway, it is quite inefficient to pass huge CLOB fields as arguments on delete operations. The ultimate solution in my mind would be to have a property tag specifying that the property should not be used in delete operations. <hibernate-mapping> <class name="com.test.Parent" table="TEST_PARENT"> <id name="identifier" type="long" unsaved-value="0" column="ID" > <generator class="sequence"> <param name="sequence">test_seq</param> </generator> </id> <version name="version" type="long" unsaved-value="negative" column="VERSION"/> <property name="name" column="NAME" type="string"/> <set name="children" lazy="true" table="TEST_CHILDREN" cascade="all,delete-orphan"> <key column="PARENT_ID" not-null="true"/> <composite-element class="com.test.ChildElement"> <property name="name" column="NAME" type="string"/> <property name="value" column="VALUE" type="text"/> </composite-element> </set> </class> </hibernate-mapping> -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira