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”.
Oracle 101: The (Very) Basics
“BULK COLLECT and FORALL are faster than cursor processing”