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.
sequenceIs 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 adescending 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.)
Since startwith is specified, the ans should be C
correct
You should create a Sequence object and Test, in stead of guessing … Practice is a good teacher than theory.
See the question 34 from 1Z0-051 (the same question)
“ans is 1. Once the max value 200 is reached then when u execute
SQL>SELECT seq1.nextval FROM dual;
it will return 1 “
enter the code in sql developer and you will see that the answer is 100
I don’t think so!
You should try this code by yourself!
create sequence seq1
start with 100
increment by 10
maxvalue 200
cycle
nocache;
—
select seq1.nextval from dual;
you are right, sorry
Don’t know the reason, but after testing, got the answer as “1”. So option A is correct.
Can anyone explain, why?
start with specify only first starting value, once the cycle repeat it will be set to default which is one
I agree with “A”. After 200 was generated the “ONE” will come.