AnyHelpWelcome: oracle insert trigger-sequence ID

terron · **Joined:** Wed Apr 25, 2007 6:50 am **Posts:** 9

Hello,

inserting to a oracle table with

auto-creation of the ID column via

trigger-sequence on the database, I get the

following error:

Quote:

11:08:17,281 DEBUG

SQLErrorCodeSQLExceptionTranslator:255 - Unable to translate SQLException with errorCode '2289', will now try the fallback translator
11:08:17,281 DEBUG SessionFactoryUtils:785 - Closing Hibernate Session
11:08:17,312 ERROR SeExamplDAOHbnt:100 - attach failed
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not get next sequence value; bad SQL grammar [select hibernate_sequence.nextval from dual]; nested exception is java.sql.SQLException: ORA-02289:

sequence does not exist

java.sql.SQLException: ORA-02289: sequence does not exist

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDesc
ribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMayb
eDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTim
eout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeI
nternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQ
uery(OraclePreparedStatement.java:3384)
at org.hibernate.id.SequenceGenerator.generate(Sequenc
eGenerator.java:75)
at org.hibernate.event.def.AbstractSaveEventListener.s
aveWithGeneratedId(AbstractSaveEventListener.java:85)
at org.hibernate.event.def.DefaultSaveOrUpdateEventLis
tener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveOrUpdateEventLis
tener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveOrUpdateEventLis
tener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:96)
at org.hibernate.event.def.DefaultSaveOrUpdateEventLis
tener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:468)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:463)
at org.springframework.orm.hibernate3.HibernateTemplat
e$18.doInHibernate(HibernateTemplate.java:693)
at org.springframework.orm.hibernate3.HibernateTemplat
e.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplat
e.saveOrUpdate(HibernateTemplate.java:690)
at com.yxz.SeExamplDAOHbnt.attachDirty(
SeExamplDAOHbnt.java:97)

The ID is defined as:

Code:

    <class name="xx.yy.SeExampl" table="SEEXAMPL" schema="SESM">
        <id name="seId" type="long">
            <column name="SE_ID" length="13"/>
            <generator class="native" />
        </id>        

I used native, since I thought that way you

don't have to care about handling the identifier...

Code:

    public void attachDirty(SeExample instance) {
   log.debug("attaching dirty SeExample instance");
   try {
            getHibernateTemplate().saveOrUpdate(instance);
       log.debug("attach successful");
   } catch (RuntimeException re) {
       log.error("attach failed", re);
       throw re;
   }
    }

Something wrong there?
Thanks

tomas@procserve · **Posted:** Tue May 15, 2007 8:35 am

You need to use the sequence generator for Oracle sequences, like this:

Code:

<generator class="sequence">
    <param name="sequence">sequence name</param>
</generator>

terron · **Joined:** Wed Apr 25, 2007 6:50 am **Posts:** 9

I don't need to generate the ID, a trigger is doing that for me
in the database.

Therefore I thought using <generator class="native"/> I will cover that...

Is there a way to avoid a generator at all for the insert but still having the property mapped for select + updates ?

Thanks!

terron · **Joined:** Wed Apr 25, 2007 6:50 am **Posts:** 9

Any help Welcome!

terron · **Joined:** Wed Apr 25, 2007 6:50 am **Posts:** 9

still looking for a solution.

After reading
http://www.hibernate.org/hib_docs/v3/re ... pping.html
not sure if there is a built in solution at all.

What I need is an attribute like insert="false", but for the </id> tag. That one is applicable for the </property> tag...

I even went that far that I got Hibernate not putting the ID in the insert query , by defining the attribute dynamic-insert="true" in the </class> tag. + using the generator identity. But it then try to get the created ID with getLong (for DB2, MySQL, ...) which the Oracle driver doesn't support and then breaks the whole.
With any other combination it always try to insert an ID.

I don't like to break the design of the database which does not allow anything to insert an ID -> It always have to be a query with the ID column missing and then a trigger in the database reads from a sequence and inserts the id afterwards.

That is a pretty common design in Oracle. I don't understand why Hibernate does not have a built-in support for that...

Do I have to create my own tag ?