TechnicalBits

NEXTVAL and CURRVAL Pseudo columns

• NEXTVAL returns the next available sequence value.
• It returns a unique value every time it is referenced, even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.
• The CURRVAL pseudo column returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.  

Accessing Sequence Values 

CREATE TABLE  sales_order (order_number NUMBER(9),      order_amount NUMBER(9),      constraint pk_sales_order PRIMARY KEY (order_number);
/* Table created.*/

INSERT INTO sales_order values (order_number_sequence.nextval, 156 );
INSERT INTO sales_order values (order_number_sequence.nextval,450 );
INSERT INTO sales_order values (order_number_sequence.nextval, 17);

SELECT * from sales_order 

ORDER_NUMBER  ORDER_AMOUNT
-------------------------------------------------------------
1 156
2 450
3 17

• Verify the sequence values by querying the USER_SEQUENCES system view with a SELECT command. 
• This view is part of the database's data dictionary.

SELECT sequence_name, min_value, max_value,increment_by, last_number FROM user_sequences; 

• Caching sequence values in memory allows faster access to those values.
• Gaps in sequence values can occur when:
• A rollback occurs
• The system crashes
• A sequence is used in another table.
• View the next available sequence, if it was created with NOCACHE, by querying the USER_SEQUENCES table.