How to get sequence number of last data entry

[not4google]

Hi all

I am writing to an Oracle database and as potentially my insert statement contains e.g. 100 rows to insert. I need to set the sequence number of the input. Is there a way of getting the last entry written to the database and adding my new row to that ID + 1 in Spring?

Thanks for any help

[dguggi]

hi,

I guess you are using a sequence (for generating your ids)? If so assuming that

last-sequence-value + 1 == next-sequence-value

is generally wrong (e.g. if you are using RAC).

If you have the options to change/modify the database you could just use a trigger which sets the id accordingly - something like this:

CREATE OR REPLACE TRIGGER "MYSCHEMA"."MYTRIGGER" 
BEFORE INSERT ON MYSCHEMA.MYTABLE    
FOR EACH ROW
BEGIN
      IF :new.id IS NULL THEN
         SELECT MYSEQUENCE.nextval INTO :new.id FROM DUAL;
      END IF;
END;
ALTER TRIGGER "MYSCHEMA"."MYTRIGGER" ENABLE

Alternatively, you could also retrieve the nextval of your sequence within your select statement like this:

insert into myschema.mytable (id) values (myschema.mysequence.nextval);

daniel

[not4google]

Thanks for your suggestion...I have checked the database in Oracle SQL Developer and under the Sequences section there definitely is a sequence which exists for my table - the definition of this sequence is as follows:

CREATE SEQUENCE  "Test_UserName"."MY_TABLE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE ;

Now in my Java database class I am doing the following:

String seq = Test_UserName.MY_TABLE_SEQ";
 jdbcTemplate
                .update(
                        "insert into TEST_TABLE (Id, Year_Month, Year_Day) values (?, ?, ?)",
new Object[] { sequence, "31-Mar-2009", 12});

This is throwing an exception as the first value passed in the following:

new Object[] { sequence, "31-Mar-2009", 12});

Is a String and therfore the call will fail. Whats the best way of getting the next sequence and passing this in as the first parameter by using:

"myschema.mysequence.nextval"?

Thanks again.

[zebthecat]

...this:

j

dbcTemplate.update(
                        "insert into TEST_TABLE (Id, Year_Month, Year_Day) values (sequence.nextval, ?, ?)",
new Object[] { "31-Mar-2009", 12});

[not4google]

Thats what I was doing in my last post - this is not allowed as the following error is thrown:

ORA-01722: invalid number

I think its expecting a number but passing in "sequence.nextval" is a String and not a number.

[zebthecat]

No it isn't - one less ? so sequence.nextval is not passed as a parameter.

[not4google]

Ahh perfect thankyou that works fine.