Bulk insert using Forall into a table with identity column causing 'not enough values' error

We have a table with identity column dm_id. Create statement is following:

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

Inside a procedure there is a cursor that selects all columns from source tables ( except identity column). Then that cursor is used while creating a type to a variable of which cursor is fetched :

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

Now the code looks like this:

    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;  

I am getting

ORA-00947- not enough values
error at
values (v_turn_hours (i));

Even though I have specified all normal columns in insert statement, I can’t run the insert. Ideally, the identity column should have generated sequntial numbers. What could be reason of the error?

>Solution :

If you aren’t inserting the identity column’s value manually, then you shouldn’t specify that column in insert at all. Also, you should specify separate values you’re inserting.

I don’t have your table(s) so I’m creating an example based on Scott’s sample schema.

This is the target table which contains an identity column:

SQL> create table target
  2    (dm_id    number generated always as identity,
  3     ename    varchar2(10),
  4     job      varchar2(15));

Table created.

PL/SQL code; note lines #13 and #14 which show what I explained earlier:

SQL> declare
  2    cursor c1 is
  3      select ename, job
  4        from emp
  5        where deptno = 10;
  6    type t_hr_hours is table of c1%rowtype;
  7    v_turn_hours t_hr_hours := t_hr_hours();
  8  begin
  9    open c1;
 10    fetch c1 bulk collect into v_turn_hours;
 11
 12    forall i in v_turn_hours.first .. v_turn_hours.last
 13      insert into target (ename, job)
 14        values (v_turn_hours(i).ename, v_turn_hours(i).job);
 15    close c1;
 16  end;
 17  /

PL/SQL procedure successfully completed.

Result:

SQL> select * From target;

     DM_ID ENAME      JOB
---------- ---------- ---------------
         1 CLARK      MANAGER
         2 KING       PRESIDENT
         3 MILLER     CLERK

SQL>