Sequence error in sql. Sequence number not allowed here

Question

I am trying to run the following query to insert a number of nodes with an id that auto-increments as nodes are loaded into the table.

However I get the error, ORA-02287: sequence number not allowed here whenever I run it.

INSERT INTO V1144Engine.T_NODES VALUES
  (
    (SELECT V1144ENGINE.S_PK_NODES.NEXTVAL FROM dual),
    1,
    'Chemistry of Life',
    0,1,
    SYSDATE,
    NULL,
    'CON.3.1',
    NULL
  );

I have tried running

SELECT V1144ENGINE.S_PK_NODES.NEXTVAL from dual

This works fine and returns the number that I want.

How do I get around this? I am running on Oracle 11g.

Also it would be much appreciated if the query were still runnable on one line as I am making these in a spreadsheet and would like to still be able to do so.

Justin Cave · Accepted Answer · 2012-06-29 19:02:15Z

23

There is no need to have the inner SELECT. Simply

INSERT INTO V1144Engine.T_NODES 
  VALUES(V1144ENGINE.S_PK_NODES.NEXTVAL,
         1,
         'Chemistry of Life',
         0,
         1,
         SYSDATE,
         null,
         'CON.3.1',
         null);

In general, though, you want to list the columns that you are providing values for in your INSERT statement. That not only documents the columns so that a future developer doesn't have to look up the order of columns in a table, it protects you if new columns are added to the table in the future.

INSERT INTO V1144Engine.T_NODES( <<list of columns>> ) 
  VALUES(V1144ENGINE.S_PK_NODES.NEXTVAL,
         1,
         'Chemistry of Life',
         0,
         1,
         SYSDATE,
         null,
         'CON.3.1',
         null);

answered Jun 29, 2012 at 19:02

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user272735 Over a year ago

The relevant Oracle documentation chapter is Sequence Pseudocolumns.

Collectives™ on Stack Overflow

Sequence error in sql. Sequence number not allowed here

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related