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

完美解决！