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
I tried the create and select queries on real database. I got “100”, not “1”.
SQL> CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
2 3 4 5 6
Sequence created.
SQL> SELECT seq1.nextval FROM dual;
NEXTVAL
———-
100
ans is 1. Once the max value 200 is reached then when u execute
SQL>SELECT seq1.nextval FROM dual;
it will return 1
try
Yes, I got “1” after seq reach max 200. Thanks.
its just Present Perfect… ->The SEQ1 sequence has generated numbers up to…
‘cos it is 1. We have already had max value 200, so SELECT seq1.nextval FROM dual; -> SELECT statement will display 1.
L2EngGrammar 😉
The minvalue by default is set as 1.
You can also give the minvalue explicitly.
Just to add extra information…
if we take off the CYCLE, it will generate an error after 200.
output will be like this if we try to calculate nextvalue:
100,110,120,130,140,150,160,170,180,190,200,1,11,21,31,41,51..,191,1,11,…..
Thanks ERIC…
Thanks a lot 🙂