Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.05

Mapping documents:
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="domain">
<class name="Customer" table="CUSTOMER">
<id name="customerId" column="CUSTOMER_ID" type="long"/>
<timestamp name="modifiedTs" column="MODIFIED_TS"/>
<property name="CustomerName" column="CUSTOMER_NAME" type="string"/>
<set name="products" table="customer_product">
<key column="CUSTOMER_ID" />
<many-to-many column="PRODUCT_ID" class="Product"/>
<sql-insert callable="true">{? = call pkg_identity_management.fn_ins_customer_x_person(?, ?)}</sql-insert>
</set>
</class>
</hibernate-mapping>


<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="domain">
<class name="Product" table="PRODUCT">
<id name="productId" column="PRODUCT_ID" type="long"/>
<timestamp name="modifiedTs" column="MODIFIED_TS"/>
<property name="ProductName" column="PRODUCT_NAME" type="string"/>
<set name="customers" table="customer_product">
<key column="PRODUCT_ID" />
<many-to-many column="CUSTOMER_ID" class="Customer"/>
</set>
</class>
</hibernate-mapping>

Name and version of the database you are using: Oracle9i Enterprise Edition Release 9.2.0.4.0
Oracle JDBC driver, version: 10.1.0.4.0

Hi,
I've got a problem with using a stored procedure with a many to many mapping. This might be a hibernate bug, but I'm happy to be proved wrong.

In the above mappings I have a very simple many-to-many mapping, with a stored procedure used to insert into the join table. (The sp line is highlighted in red).

The only way I can get the above code to work is for the sp to return a value, and to set hibernate.jdbc.batch_size = 0.

If I use a stored proc that does not return a value, then I get the following error:

WARN: org.hibernate.util.JDBCExceptionReporter - SQL Error: 17003, SQLState: null
ERROR: org.hibernate.util.JDBCExceptionReporter - Invalid column index
ERROR: org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException:

This is strange, as normally hibernate forces you to not return a value when using insert stored procedures. (See the hibernate documentation, section 17.4. In the code example for person, the sql-insert tag sp returns nothing. This is correct. The statement which follows saying you must always return a value is wrong. If you try you get another error. http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#querysql-cud)

If I then leave batching on and use a sp that returns a value I get the following error:

WARN: org.hibernate.util.JDBCExceptionReporter - SQL Error: 17090, SQLState: null
ERROR: org.hibernate.util.JDBCExceptionReporter - operation not allowed: Stored procedure with out or inout parameters cannot be batched
ERROR: org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not insert collection: [domain.Customer.products#2]
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)

I think it is pretty clear what is happening here. Hibernate is trying to batch the inserts to the join table. Each call is returning a value, which is not allowed when batching.


So for the moment, as a work around, I'm forced to turn batch updating off. I would much rather not have to do this, as I will need this optimization later. Can anyone help?

Thanks in advance,
Russell

Investigating a bit further...

I stepped through the hibernate code to find what forces the stored proc to return a value. If this requirement is removed, then batching should work again.

Class: org.hibernate.persister.collection.AbstractCollectionPersister

Function: public void recreate(PersistentCollection collection, Serializable id, SessionImplementor session)



The line callstatement.registerOutParameter(... is the problem. Not sure that it is as simple as removing this line though, as this code could be used on other situations. The TODO comment is interesting as well.

Thanks,
Russell

I had this problem too but I can't find the mapping file with the solution. I do remember that the positional parameters caused the problem. Are you currently numbering them from 0 in your java code? If you are, try numbering them from 1. Position 0 might be hardcoded for the the return code, which isn't allowed for sql-insert.

Note sure what you mean by numbering the positional parameters in the java code.

This is my customer class--very simple:



(The Product class is similar)

You aren't getting confused with a custom query using a stored proc (Like this example: http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#sp_query)? In this case, the stored procedures must return a resultset as the first out-parameter. When passing arguements to your query you could pass them in incorrectly. However in my case I am not invoking the stored proc directly--just customer.save. Here is my little test class:





Thanks,
Russell