Hello to everybody maintaining AskTom,
I hope the following script will shed some light on my question's subject which I'll admit is poorly worded to say the least.
create sequence s;
create table tt
(
id number default s.nextval,
x number
);
insert into tt(x) values(s.nextval);
commit;
select * from tt;
ID X
1 1
Is the above result normal? I was expecting the two columns to have different values.
Shouldn't every call to s.nextval result in a new and unique number?
Thanks and keep up the excellent work.
Shouldn't every call to s.nextval result in a new and unique number?
Yes and No. You're get a "new and unique number" per required invocation. In this case, we only needed to invoke it once for the insert. Here's a simpler demo
SQL> create sequence seq;
Sequence created.
SQL> create table t ( x int, y int, z int );
Table created.
SQL> insert into t values (seq.nextval,seq.nextval,seq.nextval);
1 row created.
SQL> select * from t;
X Y Z
---------- ---------- ----------
1 1 1
You can see more examples of that here
https://asktom.oracle.com/pls/apex/asktom.search?tag=insert-all-with-sequence-feature-or-featurette