OraFAQ Forum: SQL & PL/SQL » ? Nextval increments even if the insert after this fails

Home » SQL & PL/SQL » SQL & PL/SQL » ? Nextval increments even if the insert after this fails

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

? Nextval increments even if the insert after this fails [message #8523]

Thu, 28 August 2003 06:04

Rolf Winterscheidt
Messages: 2
Registered: August 2003

Junior Member

Hi,

I'm quite new in Oracle but know something more about MySQL. In MySQL I can assign an auto-increment to my IDs and everything works fine. But in Oracle it looks like I need to create my own nextvals. So I try this:

$dbh->do("insert into R_$table values (R_$table_SEQ.nextval, '$variable')") || print "ERROR! $opt_c cannot insert $variable int
o $tablen";

So far so good. But in the case the insert fails the current nextval is lost. If I repeat this, I get more and more nextvals but no inserts. So I got a loop where my actual nextval is 1000000 but I have about 10 elements in it. Do I have to program my own routine to reset the nextval to currval if something goes wrong?

Rolf

Report message to a moderator

Re: ? Nextval increments even if the insert after this fails [message #8525 is a reply to message #8523]

Thu, 28 August 2003 07:09

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Nextval is independent from your transaction. It will ALWAYS increment, no matter what you are doing with it. In fact, nextval is the next value of the sequence, not the next value of your column or whatever you assign nextval to. Is it important that you can't have gaps in your ID? I fail to see the importance of that...but then again, what do I know?

But you can never be sure of nextval because Oracle can cache (reserve) several values of a sequence for you. It is never said you'll use them all.

Anyway,
you're right, you should create your own auto-increment if you don't want gaps in your ID field sequencing. You don't have to take my word for it, but here's what Tom Kyte has to say about it. If you'd search his site (http://asktom.oracle.com) you'd encounter more examples.

HTH,
MHE

Report message to a moderator

Re: ? Nextval increments even if the insert after this fails [message #8531 is a reply to message #8525]

Thu, 28 August 2003 09:38

Rolf Winterscheidt
Messages: 2
Registered: August 2003

Junior Member

Thanks a lot, it's an interesting article.
Well, I don't have a real reason for the gap-free IDs, too. But if I have 100 Elements which are pointed to with IDs from 1 to 1000000 it just looks bad.
And the more important thing is that I always had to alter the table to number(x+1). I didn't thought I had to change is so many times :-). Maybe number(9) is enough for this.

Ok, I have realized that it's more a cosmetic design than a real problem with these high numbers.
I didn't know this from MySQL so I was surprised, but obviously it's normal :-).

Thank you,
Rolf

Report message to a moderator

Previous Topic:	SQL GURUS
Next Topic:	Virtual Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:47:04 CDT 2024