CURRVAL and NEXTVAL

Tweet

Here are some facts you may want to consider if you use or are planning to use sequences in Oracle.

You cannot use CURRVAL and NEXTVAL in the following constructs:

• A subquery in a DELETE, SELECT, or UPDATE statement
• A query of a view or of a materialized view
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY clause or ORDER BY clause
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint

Also, within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence’s initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence’s current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Related articles:

• The Lazy Developer’s Way to Populate a Surrogate Key
• When The Combination of an Oracle Sequence and a PLS_INTEGER is Deadly
• Back to basics: outer joins

Filed in Oracle on 13 Jun 05 | Tags: sequence, sql

---