Quick Quiz – Sequence NEXTVAL and CURRVAL

Answer this in your head before trying it out or looking it up. Assume the following script is run in a single session on an Oracle database:

CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1;

CREATE TABLE t (a INTEGER, b INTEGER);

-- Statement #1
INSERT INTO t VALUES (seq.NEXTVAL, seq.NEXTVAL);

-- Statement #2
INSERT INTO t VALUES (seq.CURRVAL, seq.NEXTVAL);

-- Statement #3
INSERT INTO t VALUES (seq.CURRVAL, seq.CURRVAL);

Which of the following is/are true?

1. The inserted rows will be {1,2}, {2,3} and {3,3}.
   
2. The inserted rows will be {1,1}, {2,2} and {2,2}.
   
3. The inserted rows will be {1,1}, {1,2} and {2,2}.
   
4. Statements #2 and #3 will each raise “ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session”.

Jeffrey Kemp 20 February 2006

pop-quiz-hotshot / SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.