Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Incrementing Sequence number

596777Sep 7 2007 — edited Sep 7 2007
Hi. I am reading up on Oracle's sequence number and I have a couple of questions.

1. From what I've read online, to increment a sequence number we use nextval just like the query below:

insert table_customer
(customer_seq, customer_name, customer_address)
values (select customer_seq.nextval,
'Customer ABC',
'Address XYZ');


My questions is will the insert query below will always produce the same results as above (assuming the DML are adhoc scripts, which are executed only once and no other scripts are executed for the same table).

insert table_customer
(customer_seq, customer_name, customer_address)
values ((select max(customer_seq) + 1 from table_customer),
'Customer ABC',
'Address XYZ');


2. Another question is on how nextval works. If for example I have this sequence:

seq#
1 - Row 1
2 - Row 2
3 - Row 3
5 - Row 4
6 - Row 5
8 - Row 6

There are skips in the sequence number (missing 4 and 7). When I do an insert to the table using the nextval will it insert a record with seq no = 4 (which is one of the missing seq no). Assuming that NOCACHE is set. I believe it will create seq no. 9? but I am not 100% sure.

Thanks a lot for you help. I really appreciate it.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 5 2007
Added on Sep 7 2007
2 comments
1,278 views