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?

CodePudding user response：

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>