使用FORALL批量插入含IDENTITY列的表时遇ORA-00947错误求助

我们有一张包含identity列dm_id的表，创建语句如下：

create table DM_HR_TURNS_IN_OUT_HOURS
(
  dm_id       number generated always as identity,
  action_id   NUMBER ,
  turns_emp_id NUMBER,
  action_date DATE,
  action_type VARCHAR2(2),
  log_id      NUMBER(12),
  action_day  date,
  action_Type_name varchar2(60),
  hr_emp_id        number(10),
  filial           varchar2(5),
  first_name          VARCHAR2(70),
  last_name           VARCHAR2(70),
  middle_name         VARCHAR2(70)
)

在存储过程中，我们定义了一个游标，从源表中选取除identity列外的所有字段，随后基于该游标创建类型并声明对应变量：

Cursor c1 is 
  select    
        t.id action_id, 
        t.emp_id turns_emp_id,
        t.action_date,
        t.action_type,
        t.log_id,
        trunc(action_date) action_day,
        decode(t.action_type, 'I', 'In','O','Out') action_type_name,
        e.hr_emp_id,
        e.filial,
        e.first_name,
        e.last_name,        
        e.middle_name
 from ibs.hr_turnstile_emps e ,
      ibs.hr_turns_in_out_hours t
 where  e.turns_emp_id = t.emp_id;

type t_hr_hours is table of c1%rowtype;
v_turn_hours t_hr_hours := t_hr_hours();

后续批量插入代码如下：

if c1 %isopen then 
  close c1;
end if;
open c1;
loop
  fetch c1 bulk collect 
  into v_turn_hours limit 100000;
 exit when(v_turn_hours.count = 0) ;
forall i in v_turn_hours.first .. v_turn_hours.last
insert into dm_hr_turns_in_out_hours( action_id,turns_emp_id,action_date, action_Type,log_id, action_day,
                                     action_Type_name, hr_emp_id, filial, first_name, last_name, middle_name)                                                        
 values (v_turn_hours (i));    

end loop; 
close c1;
commit;  

执行时在values (v_turn_hours (i));处出现ORA-00947: not enough values错误。尽管已指定所有非identity列，仍无法执行插入，预期identity列会自动生成序列值，请问错误原因是什么？

错误原因 #

Oracle在处理values(v_turn_hours(i))时，会把整个行类型变量v_turn_hours(i)当作单个值解析，但INSERT语句的列列表指定了12个字段，相当于要求传入12个值，实际只传入1个，因此触发ORA-00947错误。

解决方法 #

有两种简洁的修正方式：

1. 拆分行类型字段：把行类型的每个字段逐一列在values子句中，确保和列列表顺序一一对应：

forall i in v_turn_hours.first .. v_turn_hours.last
insert into dm_hr_turns_in_out_hours( action_id,turns_emp_id,action_date, action_Type,log_id, action_day,
                                     action_Type_name, hr_emp_id, filial, first_name, last_name, middle_name)                                                        
 values (
    v_turn_hours(i).action_id,
    v_turn_hours(i).turns_emp_id,
    v_turn_hours(i).action_date,
    v_turn_hours(i).action_type,
    v_turn_hours(i).log_id,
    v_turn_hours(i).action_day,
    v_turn_hours(i).action_type_name,
    v_turn_hours(i).hr_emp_id,
    v_turn_hours(i).filial,
    v_turn_hours(i).first_name,
    v_turn_hours(i).last_name,
    v_turn_hours(i).middle_name
 );    

2. 集合转表批量插入：借助table()函数将集合转换为行集，直接用SELECT语句匹配列列表，同时可以省略forall循环，代码更简洁高效：

if c1 %isopen then 
  close c1;
end if;
open c1;
loop
  fetch c1 bulk collect 
  into v_turn_hours limit 100000;
 exit when(v_turn_hours.count = 0) ;

insert into dm_hr_turns_in_out_hours( action_id,turns_emp_id,action_date, action_Type,log_id, action_day,
                                     action_Type_name, hr_emp_id, filial, first_name, last_name, middle_name)                                                        
 select * from table(v_turn_hours);

end loop; 
close c1;
commit;  

内容的提问来源于stack exchange，提问作者Sherzodbek