NEXTVAL and CURRVAL in Oracle database

NEXTVAL and CURRVAL in Oracle database

---

In Oracle, sequences are used to generate unique numeric values, often for primary keys in tables. Two important pseudo-columns associated with sequences are NEXTVAL and CURRVAL. The NEXTVAL function retrieves the next available value from the sequence and increments it each time it is called, ensuring the uniqueness of values. CURRVAL, on the other hand, returns the most recently generated value from the sequence within the current session. However, it’s important to note that CURRVAL cannot be referenced before NEXTVAL is called in the same session—doing so will result in an error: ORA-08002: sequence CURRVAL is not yet defined in this session.

To illustrate how NEXTVAL works in practice, consider the following PL/SQL block:

DECLARE
  X NUMBER;
BEGIN
  FOR I IN 2960..7160
  LOOP
    SELECT ORG_OWNR.OUS_SEQ.NEXTVAL
    INTO X
    FROM dual;
  END LOOP;
END;

In this code, a variable X is declared to hold numeric values. The FOR loop runs from 2960 to 7160, which results in 4201 iterations. Inside each loop iteration, the NEXTVAL of the sequence ORG_OWNR.OUS_SEQ is selected from the dummy DUAL table and stored in the variable X. Although X is not used further in this block, the key effect of this loop is that it advances the sequence by 4201 steps. For example, if the sequence was at value 2959 before the block started, the first NEXTVAL would return 2960, and after the final iteration, the last value fetched would be 7160. Consequently, the next time NEXTVAL is called after this block, it will return 7161. If CURRVAL is called immediately after the loop, it will return 7160, the most recent value generated in the current session. This behavior allows developers to control and track the progression of sequence numbers, especially when skipping a range, reserving IDs, or synchronizing external systems.