How Do I Create a Record Without an ID

bev · **Joined:** Fri Jun 02, 2006 11:11 am **Posts:** 3

I'm using Hibernate and Spring for the first time to write a web front end to a legacy database based on Oracle 9i, which is also written to by other back end processes.

Unfortunately, records on the database tables are created by inserting records and allowing triggers to create the primary keys from a sequence. How can I create a record via Hibernate without an id, so that the trigger & sequence can still do their job - none of the generators seem appropriate but yet I still need an ID in the mapping file.

Could anyone give me pointers on how to get round this problem?

kochcp · **Posted:** Fri Jun 02, 2006 11:38 am

Code:

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

bev · **Joined:** Fri Jun 02, 2006 11:11 am **Posts:** 3

Thanks for the quick reply.

I've put the following entry in the mapping file:

Code:

<id name="id" column="ID" type="long">
         <generator class="native">
            <param name="sequence">S_1258_1_MPAN_REPORTS</param>
         </generator>
 </id>

The trigger is as follows:

Code:

CREATE OR REPLACE TRIGGER NHHDC_1_5."TR_S_1258_1_MPAN_REPORTS" BEFORE
INSERT ON "NHHDC_1_5"."MPAN_REPORTS" FOR EACH ROW
BEGIN
  SELECT NHHDC_1_5.S_1258_1_MPAN_REPORTS.NEXTVAL
  INTO :NEW.ID
  FROM dual;
 END;
/

But I'm still getting an ORA-02289 error i.e. sequence does not exist.

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

Any ideas what I might be doing wrong?

the-gtm · **Joined:** Wed Feb 08, 2006 3:59 pm **Posts:** 75

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

The message is pretty much self explanatory !
You've got to create the sequence :

Code:

create sequence NHHDC_1_5.S_1258_1_MPAN_REPORTS

bev · **Joined:** Fri Jun 02, 2006 11:11 am **Posts:** 3

Thanks to kochcp for the help - the second problem was just due to a sequence in a nested object.

Your answer means I can at least insert a record, however, the sequence of events seems to be:
- Hibernate gets sequence required e.g. 38
- Oracle trigger fires and gets the next number in the sequence i.e. 39
- Record is inserted with the second number i.e. 39
- Hibernate returns original sequence number i.e. 38
So, the ID returned by Hibernate can not be used reliably, but I'll just have to work round this.

Thanks again.

bibroy · **Joined:** Wed May 17, 2006 3:38 am **Posts:** 45

So how did the 2nd problem got solved? You removed the sequence from the nested object and then seprately created the sequence...Is it?