[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

[Frank Durden (JIRA)]

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.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira