Hi,

I want to define my own sql-insert for some classes, in order to use id sequence generator for attributes that are not PKs. How can I do that?

My first try was this:
<sql-insert>INSERT INTO ACT_OBJVE (CAT_CODE, OWNER_ID, ACT_ID, ACT_OBJVE_IX) VALUES (?, ?, ?, ?, act_objve_index_seq.nextval)</sql-insert>

I get this error: ("Bindevariable nicht vorhanden" = binding parameter not available):

29797 DEBUG [main] org.hibernate.util.JDBCExceptionReporter - Could not execute JDBC batch update [INSERT INTO ACT_OBJVE (CAT_CODE, OWNER_ID, ACT_ID, ACT_OBJVE_IX) VALUES (?, ?, ?, ?, (act_objve_index_seq.NEXTVAL))]
java.sql.BatchUpdateException: ORA-01006: Bindevariable nicht vorhanden

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:458)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:172)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:678)
at c2iedm.hibernate.test.ActionObjectiveTestCase.tearDown(ActionObjectiveTestCase.java:47)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:474)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)
29797 WARN [main] org.hibernate.util.JDBCExceptionReporter - SQL Error: 1006, SQLState: 72000
29797 ERROR [main] org.hibernate.util.JDBCExceptionReporter - ORA-01006: Bindevariable nicht vorhanden

Ok, i understand that - but how can I state that this is not a varaiable but a a statement to be executed by Oralce (in plain SQl it works).

I also tried it with a subselect:
INSERT INTO ACT_OBJVE (CAT_CODE, OWNER_ID, ACT_ID, ACT_OBJVE_IX) VALUES (?, ?, ?, ?, (SELECT act_objve_index_seq.nextval from dual))

that doesn't work in hibernate neither in plain SQL ("sequence number not allowed here")

I guess it's pretty easy to fix - but I'm running out of ideas.
Thanks for some help!

Hibernate version:
3.2

Name and version of the database you are using:
Oracle 9