Hello,

When using Powerbuilder with Oracle, in some cases oracle skips numbers.
What I mean is that when the ID column has a value of 100, instead of
getting the next value(101) I get 102 or 103 anything but not the next. It
updates the same row from 100 to 103 and of course as you might imagine I
can not retrieve the row of 100. How this problem may be solved(Not updating
the id column to a different value).

Thanks in advance.

"vasilis" <vasilisk (AT) ics (DOT) forth.gr> wrote

meaningless number.
There are several reasons.
1. If some other session did an insert then a rollback the number would
"skip".
2. If the sequence cache gets aged out of the shared pool then it would
"skip".
3. If you shut down and restart the db then the cached values will be
"lost".

You can solve it, but the scalability of the application will go down the
tubes.

Again, why do you care? You shouldn't be relying on it not skipping.
Jim

"vasilis" <vasilisk (AT) ics (DOT) forth.gr> wrote

meaningless number.
There are several reasons.
1. If some other session did an insert then a rollback the number would
"skip".
2. If the sequence cache gets aged out of the shared pool then it would
"skip".
3. If you shut down and restart the db then the cached values will be
"lost".

You can solve it, but the scalability of the application will go down the
tubes.

Again, why do you care? You shouldn't be relying on it not skipping.
Jim

"vasilis" <vasilisk (AT) ics (DOT) forth.gr> wrote

meaningless number.
There are several reasons.
1. If some other session did an insert then a rollback the number would
"skip".
2. If the sequence cache gets aged out of the shared pool then it would
"skip".
3. If you shut down and restart the db then the cached values will be
"lost".

You can solve it, but the scalability of the application will go down the
tubes.

Again, why do you care? You shouldn't be relying on it not skipping.
Jim

"vasilis" <vasilisk (AT) ics (DOT) forth.gr> wrote

meaningless number.
There are several reasons.
1. If some other session did an insert then a rollback the number would
"skip".
2. If the sequence cache gets aged out of the shared pool then it would
"skip".
3. If you shut down and restart the db then the cached values will be
"lost".

You can solve it, but the scalability of the application will go down the
tubes.

Again, why do you care? You shouldn't be relying on it not skipping.
Jim

Thanks for your response,

I care about it because when I retrieve the row I use the first value
inserted not the updated one which might be or might be not the nextval on
the same cell. Since you say I can solve it do you know how?

Thanks you
"news.verizon.net" <kennedyii (AT) verizon (DOT) net> wrote

Thanks for your response,

I care about it because when I retrieve the row I use the first value
inserted not the updated one which might be or might be not the nextval on
the same cell. Since you say I can solve it do you know how?

Thanks you
"news.verizon.net" <kennedyii (AT) verizon (DOT) net> wrote

Thanks for your response,

I care about it because when I retrieve the row I use the first value
inserted not the updated one which might be or might be not the nextval on
the same cell. Since you say I can solve it do you know how?

Thanks you
"news.verizon.net" <kennedyii (AT) verizon (DOT) net> wrote

Thanks for your response,

I care about it because when I retrieve the row I use the first value
inserted not the updated one which might be or might be not the nextval on
the same cell. Since you say I can solve it do you know how?

Thanks you
"news.verizon.net" <kennedyii (AT) verizon (DOT) net> wrote

On Feb 12, 9:53 am, "vasilis" <vasil... (AT) ics (DOT) forth.gr> wrote:
Oracle does not skip numbers, your application does (helped
by the end user):
User: "let's do an insert"
Application: "Ok - you'll need a new number then"
RDBMS: "Here's 101 - the next one will be 102"
User: "time for coffee..."
....[other users happily inserting, and using 102, 103, 104...]
User: "What's this screen? Let's clear it and start over"
Application: "OK all values reset and cleared"

Meaning: your user just "used" number 101. Your app reserved that
number
by requesting it too early, it should have requested it at commit time.