Problem with clob inside composite-element

tibounet · **Joined:** Sun Apr 02, 2006 7:09 am **Posts:** 1

Hi,

after two days of searching on the Hibernate site (yes, I've read the special page on clobs and blobs) and throughout the web, I cannot find a suitable solution to the following problem:

we declare a table "Additionaldata" as a composite-element class of a parent class "event" as in the following mapping. Then, Hibernate generates SQL queries for Additionaldata that do not take into account the fact that we're using Oracle, so it creates e.g. a query such as "delete from additionaldata where event_id=? ,group_num=?, meaning_id=?, type_id=?, value=?, update_time=?".

As it is forbidden to make a strict comparison "=" between CLOBs in Oracle, we obtain a ORA-00932 error (inconsistent datatypes : expected - got CLOB).

We have a means to prevent that, because the triple (event_id, group_num and meaning_id) is unique for every row in AdditionalData. I believe that if I declared this unique composite key, the generated query
would not contain value anymore.

However, I cannot figure out how to declare this composite key inside the composite-element - or should it be outside?

Thanks in advance for your help!

Hibernate version : 3.0.5

DB : Oracle 9.2.0.7

Dialect : Oracle9Dialect

Code:

<?xml version="1.0"?>

<!-- $Id: Event.hbm.xml 2449 2006-03-31 10:12:00Z sii265 $ -->

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="foo.bar" schema="foo" >

   <class name="Event" table="Events">

        <cache usage="nonstrict-read-write"/>

                <id name="id" type="long">
                        <generator class="sequence">
                                <param name="sequence">SEQ_EVENTS_ID</param>
                        </generator>
                </id>

                <property name="level"   type="integer"              column="level" />
                <property name="createTime" type="timestamp"            column="create_time" />
        <property name="messageid"  type="string" length="1000" column="messageid" />
        <property name="status"     type="integer"              column="status" />


            <set name="additionalData" table="additionalData" cascade="all-delete-orphan" lazy="false">
                <key column="event_id" />
                <composite-element class="AdditionalData" >
                    <parent name="event" />
                    <property    name="group"                              column="group_num"   />
                    <many-to-one name="meaning"    class="PropertyMeaning" column="meaning_id"  />
                    <many-to-one name="type"       class="PropertyType"    column="type_id"     />
                    <property    name="value"      type="text"             column="value"       />
                    <property    name="updateTime" type="timestamp"        column="update_time" />
                </composite-element>
            </set>

       </class>
</hibernate-mapping>

fdurden · **Joined:** Thu Nov 17, 2005 1:44 pm **Posts:** 6

Hi,

I have pretty much the same problem as tibounet using Hibernate 3.1.2. I tried to fix it using a stored procedure for the delete part as in

Code:

<sql-delete callable="true">{? = call delete_xxx (?, ?, ?, ?, ?, ?)}</sql-delete>

but this works as long as the CLOB field is <32k. If it's >32k the PL/SQL chokes on the argument and gives me a "ORA-01460: unimplemented or unreasonable conversion requested". This using a OCI driver. But anyway, it seems pretty pointless to pass the CLOB value as an argument of the SQL delete if it's not part of the object identity. The most user-friendly solution to this would be a tag indicating if the property should be part of an SQL delete. Something like:

Code:

<property name="bigClob" type="text" column="bigValue" required-in-delete="false"/>

Any workaround suggestions are welcome.

sorin_postelnicu · **Joined:** Tue Feb 03, 2009 9:26 am **Posts:** 3

I had the same problem and after searching/trying/debugging/browsing/reading I discovered the following paragraph in the hibernate manual about components:

Quote:

A composite element mapping does not support null-able properties if you are using a <set>.
There is no separate primary key column in the composite element table.
Hibernate uses each column's value to identify a record when deleting objects, which is not possible with null values.
You have to either use only not-null properties in a composite-element or choose a <list>, <map>, <bag> or <idbag>.

And it immediately ringed a bell: this problem with CLOB columns inside composite-element is similar to the problem with having null values.

So I tried the suggestion to use a "bag" (with List) instead o a "set" and it worked!