CURRVAL

In Oracle, CURRVAL is a function that returns the current value of a sequence, i.e., the most recently generated value by NEXTVAL within the same session. It is typically used in conjunction with the NEXTVAL function.

Here’s an in-depth look at how CURRVAL works in Oracle:

What is CURRVAL?

• CURRVAL is a function used to retrieve the current value of a sequence. It returns the last value that was generated using NEXTVAL in the current session.
• Important: You cannot use CURRVAL until you have first used NEXTVAL in the session. If you try to use CURRVAL without calling NEXTVAL first, Oracle will raise an error.

Syntax of CURRVAL

sequence_name.CURRVAL

Here, sequence_name is the name of the sequence from which you want to retrieve the current value.

How CURRVAL Works

1. Dependency on NEXTVAL:
• CURRVAL always works in conjunction with NEXTVAL. When you call NEXTVAL, the sequence is incremented, and the current value of the sequence is updated.
• After calling NEXTVAL, you can then retrieve the current value using CURRVAL.
2. Scope of CURRVAL:
• CURRVAL only works within the current session. Each session has its own sequence value. If one session calls NEXTVAL, other sessions won't be able to access that session's current value using CURRVAL.
• In other words, CURRVAL will return the last value that was generated for the sequence in the current session. If you restart your session, the current value will be reset.
3. Usage of CURRVAL:
• Once you have invoked NEXTVAL, CURRVAL can be used to access the most recent value of the sequence.
• Typically, it’s used after NEXTVAL to refer to the current sequence value within the same transaction or session.

Example of Using CURRVAL

Create a sequence:

CREATE SEQUENCE emp_seq

START WITH 1000

INCREMENT BY 1;

Retrieve NEXTVAL:

SELECT emp_seq.NEXTVAL FROM dual;

This will increment the sequence and return the next value, say 1000.

Retrieve CURRVAL: After calling NEXTVAL, you can then retrieve the current value of the sequence using CURRVAL:

SELECT emp_seq.CURRVAL FROM dual;

This will return 1000 (the most recent value of emp_seq after the NEXTVAL call).

Key Points to Remember about CURRVAL:

1. Must use NEXTVAL before CURRVAL:
• If you attempt to use CURRVAL before calling NEXTVAL in the same session, Oracle will raise an error. For example:

o   SELECT emp_seq.CURRVAL FROM dual;  -- Error: must call NEXTVAL first

The correct order of operations is:

SELECT emp_seq.NEXTVAL FROM dual;  -- Increments the sequence and returns 1000

SELECT emp_seq.CURRVAL FROM dual;  -- Returns the current value (1000)

2. Session-specific:
• CURRVAL is session-specific. If another session calls NEXTVAL, the CURRVAL in your session will not change.
• If you restart your session, the current value for that session will be reset, and CURRVAL will start from the new session's NEXTVAL call.
3. Cannot be used with a sequence that has not been used:
• If NEXTVAL has not been called in a session, attempting to use CURRVAL will result in an error. This is to ensure consistency since CURRVAL is intended to reflect the most recent sequence value generated by NEXTVAL in that session.
4. Not used for sequence management:
• CURRVAL does not allow you to modify or reset the sequence value. It only provides access to the current value. Sequence management (like altering, dropping, or resetting the sequence) must be done using DDL statements (e.g., ALTER SEQUENCE, DROP SEQUENCE).
5. Use Cases:
• Logging purposes: You might use CURRVAL when inserting rows into a table and logging the sequence value.
• Referencing the sequence value: If you're inserting multiple rows into related tables and need to reference the generated sequence value multiple times within a single transaction, CURRVAL can be helpful.

Example:

INSERT INTO employees (employee_id, name)

VALUES (emp_seq.NEXTVAL, 'John Doe');

 

INSERT INTO employee_log (employee_id, log_message)

VALUES (emp_seq.CURRVAL, 'Employee John Doe added');

Restrictions of CURRVAL:

1. Can only be used after NEXTVAL:
• You cannot directly get CURRVAL without invoking NEXTVAL first. Oracle prevents using CURRVAL before NEXTVAL because no value exists for the sequence until NEXTVAL is called.
2. Error when used across sessions:
• If you try to use CURRVAL across different sessions (for example, you call NEXTVAL in one session and CURRVAL in another), it will result in an error, as CURRVAL is specific to the session where NEXTVAL was invoked.

Example Code to Demonstrate Usage:

Create a sequence:

CREATE SEQUENCE emp_seq

START WITH 1

INCREMENT BY 1;

Use NEXTVAL to get a value:

SELECT emp_seq.NEXTVAL FROM dual;  -- Returns 1

Now use CURRVAL to get the current value:

SELECT emp_seq.CURRVAL FROM dual;  -- Returns 1

Using NEXTVAL again:

SELECT emp_seq.NEXTVAL FROM dual;  -- Returns 2

Again, use CURRVAL:

SELECT emp_seq.CURRVAL FROM dual;  -- Returns 2

 

Practical Considerations:

• Using CURRVAL with INSERT Statements: You might want to get the current sequence value after inserting a row, particularly if you're working with tables that reference each other via foreign keys.

Example:

INSERT INTO orders (order_id, customer_id)

VALUES (emp_seq.NEXTVAL, 101);

 

INSERT INTO order_log (order_id, log_date)

VALUES (emp_seq.CURRVAL, SYSDATE);

• Session-specific behavior: Since CURRVAL is tied to the session, it's important to note that the sequence's value is not shared across different database connections or sessions.

 

Conclusion:

• CURRVAL provides a way to retrieve the current sequence value after using NEXTVAL in the same session. It’s crucial to understand that CURRVAL cannot be used until NEXTVAL is called, and it is always session-specific.
• You can use CURRVAL to retrieve and work with the sequence values for tasks like logging or foreign key management in applications, as long as NEXTVAL has been invoked first in your session.