How can I insert multiple rows into oracle with a sequence value?

Question

I know that I can insert multiple rows using a single statement, if I use the syntax in this answer.

However, one of the values I am inserting is taken from a sequence, i.e.

insert into TABLE_NAME
(COL1,COL2)
select MY_SEQ.nextval,'some value' from dual
union all
select MY_SEQ.nextval,'another value' from dual
;

If I try to run it, I get an ORA-02287 error. Is there any way around this, or should I just use a lot of INSERT statements?

EDIT:
If I have to specify column names for all other columns other than the sequence, I lose the original brevity, so it's just not worth it. In that case I'll just use multiple INSERT statements.

See also http://stackoverflow.com/questions/8292199/query-several-nextval-from-sequence-in-one-satement if you got here and just want to select multiple different unique sequence nextval's in the same query... — rogerdpack, Jun 25 '13 at 17:10

score 41 · Accepted Answer · answered Oct 23 '08 at 02:06

41

This works:

insert into TABLE_NAME (COL1,COL2)
select my_seq.nextval, a
from
(SELECT 'SOME VALUE' as a FROM DUAL
 UNION ALL
 SELECT 'ANOTHER VALUE' FROM DUAL)

answered Oct 23 '08 at 02:06

WW.

23,793
13
94
121

Getting the following error: `java.sql.BatchUpdateException: ORA-02287: sequence number not allowed here` – beckah Jun 26 '15 at 13:44
@beckah ask a new question showing your SQL and Oracle version? – WW. Jun 26 '15 at 14:06
1

figured it out! structuring the inserted sequence value not as a query but simply as 'sequencer.nextval' as an insert value worked great :) @WW – beckah Jun 26 '15 at 14:39

Dilshod Tadjibaev · Answer 2 · 2008-10-26T05:58:03.440

It does not work because sequence does not work in following scenarios:

In a WHERE clause
In a GROUP BY or ORDER BY clause
In a DISTINCT clause
Along with a UNION or INTERSECT or MINUS
In a sub-query

Source: http://www.orafaq.com/wiki/ORA-02287

However this does work:

insert into table_name
            (col1, col2)
  select my_seq.nextval, inner_view.*
    from (select 'some value' someval
            from dual
          union all
          select 'another value' someval
            from dual) inner_view;

Try it out:

create table table_name(col1 varchar2(100), col2 varchar2(100));

create sequence vcert.my_seq
start with 1
increment by 1
minvalue 0;

select * from  table_name;

score 4 · Answer 3 · answered Oct 23 '08 at 02:10

4

insert into TABLE_NAME
(COL1,COL2)
WITH
data AS
(
    select 'some value'    x from dual
    union all
    select 'another value' x from dual
)
SELECT my_seq.NEXTVAL, x 
FROM data
;

I think that is what you want, but i don't have access to oracle to test it right now.

answered Oct 23 '08 at 02:10

EvilTeach

28,120
21
85
141

What version of Oracle does that require? – Chris Noe Oct 23 '08 at 02:13

score 3 · Answer 4 · edited Jan 09 '11 at 18:29

3

From Oracle Wiki, error 02287 is

An ORA-02287 occurs when you use a sequence where it is not allowed.

Of the places where sequences can't be used, you seem to be trying:

In a sub-query

So it seems you can't do multiples in the same statement.

The solution they offer is:

If you want the sequence value to be inserted into the column for every row created, then create a before insert trigger and fetch the sequence value in the trigger and assign it to the column

edited Jan 09 '11 at 18:29

skaffman

398,947
96
818
769

answered Oct 23 '08 at 01:57

billjamesdev

14,554
6
53
76

I've done this before. It's a pain, but it seems to be about the only way around it. – madlep Oct 23 '08 at 03:48

score 1 · Answer 5 · answered Oct 23 '08 at 01:56

1

A possibility is to create a trigger on insert to add in the correct sequence number.

answered Oct 23 '08 at 01:56

Brian Schmitt

6,008
1
24
36

score 0 · Answer 6 · answered Jul 20 '12 at 13:54

this works and there is no need to use union all.

Insert into BARCODECHANGEHISTORY (IDENTIFIER,MESSAGETYPE,FORMERBARCODE,NEWBARCODE,REPLACEMENTDATETIME,OPERATORID,REASON)
select SEQ_BARCODECHANGEHISTORY.nextval, MESSAGETYPE, FORMERBARCODE, NEWBARCODE, REPLACEMENTDATETIME, OPERATORID, REASON
from (
  SELECT
    'BAR' MESSAGETYPE,
    '1234567890' FORMERBARCODE,
    '1234567899' NEWBARCODE,
    to_timestamp('20/07/12','DD/MM/RR HH24:MI:SSXFF') REPLACEMENTDATETIME,
    'PIMATD' OPERATORID,
    'CORRECTION' REASON
  FROM dual
);

really? How would you insert more than one row in a single statement without using union? — Ovesh, Jul 21 '12 at 13:56

How can I insert multiple rows into oracle with a sequence value?

6 Answers6

Linked