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.
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.
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
);
A possibility is to create a trigger on insert to add in the correct sequence number.
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.
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
It does not work because sequence does not work in following scenarios:
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;
A possibility is to create a trigger on insert to add in the correct sequence number.
It does not work because sequence does not work in following scenarios:
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;
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
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
);
A possibility is to create a trigger on insert to add in the correct sequence number.
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
A possibility is to create a trigger on insert to add in the correct sequence number.
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.
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
Source: Stackoverflow.com