oracle自动生成序列号，批量insert，解决ORA-02287

项目场景：

oracle 数据库，某个表中发现缺失了一批数据，需要补全近半年的。
由于是固定的数据，只是日期差异，决定批量insert
—
表中使用了序列自动生成ID

问题描述

以下方法都试过，都报错

方法1：
INSERT ALL
INTO my_table (id, data_column) VALUES (my_sequence.NEXTVAL, ‘Data 1’)
INTO my_table (id, data_column) VALUES (my_sequence.NEXTVAL, ‘Data 2’)
INTO my_table (id, data_column) VALUES (my_sequence.NEXTVAL, ‘Data 3’)
SELECT * FROM dual;
此方法不行，会报错

方法2：
INSERT INTO my_table (id, data_column)
select id, data_column from (
SELECT my_sequence.NEXTVAL as id, ‘Data 1’ as data_column FROM dual
UNION
SELECT my_sequence.NEXTVAL as id, ‘Data 2’ as data_column FROM dual
UNION
SELECT my_sequence.NEXTVAL as id, ‘Data 3’ as data_column FROM dual
) ;
报错：ORA-02287:sequence number not allowed here

---

原因分析：

ORA-02287:sequence number not allowed here
ORA-02287:此处不允许有序列号

查了资料发现：需要把my_sequence.NEXTVAL提到UNION的外层

---

解决方案：

把生成序列提出来，不在UNION那层，便成功自动生成ID

INSERT INTO my_table (id, data_column)
select my_sequence.NEXTVAL as id, dt.*
from (
SELECT ‘Data 1’ as data_column FROM dual
UNION
SELECT ‘Data 2’ as data_column FROM dual
UNION
SELECT ‘Data 3’ as data_column FROM dual
) dt