What you can’t do with NEXTVAL

I wrote a quiz for the PL/SQL challenge on sequence pseudocolumns, and I noticed that a couple of the answers tricked a lot of people who took the quiz. Time for a blog post on what you can’t do with NEXTVAL!

If you want to try the quiz, do it now before reading on:

Pseudocolumns: Sequence Pseudocolumns from “Weekly SQL Quiz: 31 January – 6 February 2015”.

Suppose that you have a table with rows already in it, and you want to populate an existing column with unique values for each row of the table. Sequences are an obvious way to do this, particularly the NEXTVAL pseudocolumn.

Here are the table and sequence I created to play with:

CREATE TABLE plch_myjoin AS
SELECT * FROM
  ( SELECT 0 AS myid,
      decode (object_type , 'TABLE',10, 'INDEX', 20,'VIEW',30,40) AS deptno,
      object_type AS dname,
      object_id AS empno,
      object_name AS ename
    FROM all_objects
    WHERE owner='SYSTEM'
      AND object_type IN ('TABLE','INDEX','VIEW','SYNONYM')
  )
WHERE rownum < 30
/
CREATE SEQUENCE plch_myseq
/

Now, I want the myid column to have a unique value for each row of the table. I can just do this:

UPDATE plch_myjoin SET myid = plch_myseq.nextval
/

But, this post is about what I can’t do with NEXTVAL to get that result.

A couple places where you can’t use NEXTVAL

First, there are are a couple of ways you might try to use NEXTVAL that will result in ORA-02287: sequence number not allowed here, for which Oracle offers the following extremely helpful help text:

SQL> !oerr ora 2287
02287, 00000, "sequence number not allowed here"
// *Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
//	  here in the statement.
// *Action: Remove the sequence number.

In a single-row subquery in update statement:

UPDATE plch_myjoin SET myid = (SELECT plch_myseq.nextval FROM dual)
/
 
ERROR at line 1:
ORA-02287: SEQUENCE NUMBER NOT allowed here

Do this instead:

UPDATE plch_myjoin SET myid = plch_myseq.nextval
/

In merge like this:

MERGE INTO plch_myjoin m
USING ( SELECT plch_myseq.nextval AS myid, 
        deptno, dname, empno, ename
        FROM plch_myjoin ) j
ON ( m.empno=j.empno )
WHEN matched THEN UPDATE SET m.myid = j.myid
/
 
ERROR at line 1:
ORA-02287: SEQUENCE NUMBER NOT allowed here

Instead, you can use NEXTVAL in merge like this:

MERGE INTO plch_myjoin m
USING ( SELECT * FROM dual ) j
ON ( 1=1 )
WHEN matched THEN UPDATE SET m.myid = plch_myseq.nextval
/

And a place where you can use NEXTVAL but it won’t do what you want

This tripped up a lot of people. Suppose I create this function:

CREATE OR REPLACE FUNCTION plch_get_new_id_val RETURN NUMBER
IS
  BEGIN
   RETURN plch_myseq.nextval;
  END;
/

Now if I select plch_get_new_id_val from dual, it will return the nextval in the sequence, right? Right. So, will the following SQL work to populate each row in the table with a unique sequence value?

UPDATE plch_myjoin SET myid = (SELECT plch_get_new_id_val FROM dual)
/

No! This updates all rows in the table with the same single value – whatever plch_myseq.nextval returned at the time the update statement was run. Tricky! Use the update statement above instead:

UPDATE plch_myjoin SET myid = plch_myseq.nextval
/

3 comments

  1. Connor says:

    One other interesting thing is that “nextval” is not necessarily evaluated each time its “seen”…eg

    insert all
    into ….. values (seq.nextval)
    into ….. values (seq.nextval)
    into ….. values (seq.nextval)
    into ….. values (seq.nextval)

    Each of the “nextval” will return the SAME value.

    Cheers,
    Connor

    • Natalka says:

      Thanks Connor, that’s a good one! And potentially a very useful behaviour… or annoying behaviour, depending on what you wanted the INSERT ALL to do.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.