oracle 序列值批量增加,Oracle数据库批量插入使用Sequence.nextval报错ORA:02287问题解决...

今天出现一个很奇葩的问题，之前做oracle的批量插入,对于主键都是直接在Java中赋值，后来发现不方便，后来改用Sequence.nextval，结果报错

使用前:

insert into exceltable (id,chinese,english)

select 1,'111','111' from dual union all

select 2,'222','222' from dual

没有报错！！！

使用序列后:

insert into exceltable (id,chinese,english)

select excel_seq.nextval,'111','111' from dual union all

select excel_seq.nextval,'222','222' from dual

报错---ORA:02287

先创建函数:

create or replace function get_seq (p_in_sqname in varchar2) return number

is

l_res number ;

begin

execute immediate 'select '|| p_in_sqname|| '.nextval from dual' into l_res ;

return l_res ;

end ;

然后再如下调用：

insert into exceltable (id,chinese,english)

select get_seq('excel_seq'),'111','111' from dual union all

select get_seq('excel_seq'),'222','222' from dual 完美解决！