Problem Explanation:

I am setting up a table to do insert using Oracle Sequence.The index field (<id> field) is getting different values than what is retreived by sequence.nextval DB call. I am using Oracle 10g, Hibernate (3.0.5). The column is question is the primary key, a NUMBER(10). The underlying property is a Long, set to null (as it is a new object). The generator class is set to a sequence, pointing to a value Oracle sequence. No errors are being generated when I do the .save.

For example, when I debug the following snippet, I get a ID value = 10 from DB (the Hibernate log shows call made to DB sequence obj). The value gets assigned to the batchItemId property in the value object (PaymentBatchItem) below.

But when Hibernate commits this transaction. the actual value inserted for the corresponding record has ID = 11. It skips the value retrieved by sequence object call for the same Insert..!

It almost seems that Hibernate makes a 1st call to sequence.nextVal, it increments the sequence by 1 then again when it calls a Insert statement, it does not take into account the previously retreived sequence #.

I have not read about this issue on the forum before. So, I think I might be missing something.

I tried changing my ID columns datatype to Integer instead of Long, tried to flush the session but I am getting the same problem.

Any help is appreciated.

thanks.


Hibernate version: 3.0.5

Mapping documents: PaymentBatchItem.hbm.xml
<class name="com.PaymentBatchItem" table="payment_batch_item">

<id name="batchItemId" type="long" column="batch_item_id">
<generator class="sequence">
<param name="sequence">seq_batch_item_id</param>
</generator>
</id>

<property name="loan">
<column name="loan" sql-type="varchar" length="15" not-null="true"/>
</property>

<property name="batchDate">
<column name="batch_dt" sql-type="datetime" not-null="true"/>
</property>

<property name="batchItemNumber">
<column name="batch_item_nbr" sql-type="integer" not-null="true"/>
</property>

<property name="draftId" type="long">
<column name="draft_id" not-null="false"/>
</property>

<property name="traceNumber" type="long">
<column name="trace_nbr" not-null="false"/>
</property>

</class>

Code between sessionFactory.openSession() and session.close():

public static List insert(List batchItems)
{
Session sess = null;

try
{
sess = HibernateUtil.getSession(HibernateUtil.DATASOURCE_EPS);

HibernateUtil.beginTransaction(HibernateUtil.DATASOURCE_EPS);

Iterator it = batchItems.iterator();
while(it.hasNext())
{
PaymentBatchItem pbi = (PaymentBatchItem) it.next();
//Long id = (Long) sess.save(pbi);
sess.saveOrUpdate(pbi);
}

HibernateUtil.commitTransaction(HibernateUtil.DATASOURCE_EPS);

} catch(HibernateException he)
{
HibernateUtil.rollbackTransaction(HibernateUtil.DATASOURCE_EPS);

he.printStackTrace(System.err);
} finally
{
HibernateUtil.closeSession(HibernateUtil.DATASOURCE_EPS);
}
return batchItems;

}

Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true):
Hibernate: select seq_batch_item_id.nextval from dual
Hibernate: select seq_batch_item_id.nextval from dual
Hibernate: select seq_batch_item_id.nextval from dual
EPS transaction present.
Hibernate: insert into payment_batch_item (loan, batch_dt, batch_item_nbr, draft_id, trace_nbr, batch_item_id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into payment_batch_item (loan, batch_dt, batch_item_nbr, draft_id, trace_nbr, batch_item_id) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into payment_batch_item (loan, batch_dt, batch_item_nbr, draft_id, trace_nbr, batch_item_id) values (?, ?, ?, ?, ?, ?)

I would appreciate if someone can reply to this issue.

I have been debugging the hibernate session.save() source code.I still think that I might be missing some configuration property, which I can find by checking the source code but so far I have been unsuccessful.

It seems none of you all have faced the same problem.

The problem was in my Hiberate Config file. I am specifying Oracle 9i related Dialect class but we recently upgraded the Oracle DB to 10.2g version.

<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>

It was not so obvious initally since I thought that there is no difference in the way Oracle 9i handles sequence when compared with Oracle 10.2g.

But after hours of unsuccessfuly debugging, I ran a simple test to insert a record in 9i DB instance. It stored proper sequence number in the 9i DB.

So, this finding presents a new question..

is there a hibernate dialect available for Oracle 10.2g or 10g version? If not then what is the alternative for us?

thanks.

Create a working dialect by sub-classing the Oracle9 Dialect. Get it working correctly (should be a no brainer) and submit it to JIRA (assuming no one else has completed this before you have.

Our DBAs confirmed that there is no difference in the way oracle handles sequences in 9i and 10g versions.

There is certainly something different in the way Hibernate handles 10g calls(ofcourse through 9i dialect for now) for fetching sequences.

I can subclass Oracle9iDialect but what else needs to be done to fix this sequence skipping issue in 10g?

I do see the calls Hibernate Oracle9 dialect makes to fetch next sequence # - "select <sequencename>.nextval from dual", which is consistent with both the versions of oracle DB.

FYI..
I tested the sequence skipping problem using 9i and 10g versions of JDBC drivers and I get the same problem with both the drivers.

There was a trigger set to move the sequence to nextval before any insert. This was causing the sequence no. to skip.

I did not implement trigger in the test I ran on 9i version, which obviously would work.

We wasted quite a bit of time looking in the Hibernate and our application code. There is no issue with Oracle9 Dialect in dealing with sequences.

Thanks for your help.