Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL
statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A.
1
B.
10
C.
100
D.
an error
Explanation:
But why the answer is not “C” ?
Because you didn’t specify the MINVALUE for the sequence. If you check the sequence definition that you
created it will have the default value of 1, which it reverts to when cycling.
If you wanted to keep the minimum value you would need to specify it in the sequence creation.
sequence Is the name of the sequence generator
INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is
omitted, the sequence increments by 1.)
START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence
starts with 1.)
MAXVALUE n Specifies the maximum value the sequence can generate
NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending
sequence (This is the default option.)
MINVALUE n Specifies the minimum sequence value
NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending
sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum
or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By
default, the Oracle server caches 20 values.)