1Z0-071 practice question 27 of 35

Which statement will return the last sequence number generated by the EMP_ SEQ sequence?

Full set: 326 questions
Answer options
A: SELECT NEXTVAL FROM emp_ seq;
B: SELECT CURRVAL FROM emp_ seq;
C: SELECT emp_ seq. CURRVAL FROM DUAL; Correct answer
D: SELECT emp_ seq . NEXTVAL FROM DUAL;
Correct Answer:
C. SELECT emp_ seq. CURRVAL FROM DUAL;
Explanation

The CURRVAL pseudocolumn is used to retrieve the most recent sequence number that was generated for the current session by a call to NEXTVAL. The correct syntax to select a value from a sequence is sequencename.pseudocolumn. Since pseudocolumns are not part of a real table, the DUAL table is used as a placeholder to complete the SELECT statement syntax. Therefore, SELECT empseq.CURRVAL FROM DUAL; correctly retrieves the last generated sequence number for the session.

Why Incorrect

A. SELECT NEXTVAL FROM empseq; is syntactically incorrect and NEXTVAL generates a new sequence number, it does not return the last one.
B. SELECT CURRVAL FROM empseq; is syntactically incorrect. The sequence name must be used as a prefix to the pseudocolumn.
D. SELECT empseq.NEXTVAL FROM DUAL; generates a new sequence number, rather than returning the last one generated in the session.

References

1. Oracle Database SQL Language Reference, 21c. "Pseudocolumns". The section on CURRVAL and NEXTVAL states: "You must qualify CURRVAL and NEXTVAL with the name of the sequence... For example, employeesseq.nextval".

2. Oracle Database SQL Language Reference, 21c. "DUAL Table". It describes DUAL as a table automatically created by Oracle Database that is "useful for selecting a pseudocolumn such as SYSDATE or USER". The same principle applies to sequence pseudocolumns.

Scroll to Top