How can I insert multiple lines into oracle with sequence value?

Question

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

However, one of the values ​​that I insert is taken from the 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 start it, I get error ORA-02287. Is there a way around this, or should I just use a lot of INSERT statements?

EDIT:
If I need to specify column names for all other columns other than a sequence, I lose my original brevity, so it just isn't worth it. In this case, I just use a few INSERT statements.

Answer 1

It 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) 

Answer 2

This does not work because the sequence does not work in the following scenarios:

• In the WHERE clause
• In a GROUP BY or ORDER BY clause
• In the DISTINCT clause
• Along with UNION or INTERSECT or MINUS
• In subquery

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

However, this works:

 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:

 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; 

Answer 3

 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 this is what you want, but I do not have access to oracle to check it right now.

Answer 4

From Oracle Wiki , error 02287

ORA-02287 occurs when you use a sequence where this is not permitted.

From places where sequences cannot be used you seem to be trying:

In subquery

So it seems that you cannot do the multiplicity in one expression.

Proposed Solution:

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

Answer 5

Ability to create a trigger for insertion to add to the correct sequence number.

Answer 6

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

 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 );