CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
kera_404
kera_404

Reputation: 123

Bulk Inserts with Oracle

I have a Table EMPLOYEE with 4800 records, having two columns emp_id (1:4800) and status(0 for all records). I am implementing bulk collect with a limit 1000, so for every 1000 records collected status of all the corresponding status is set to 1. this is my code

declare
    TYPE bulk_emp_id is table of employee%rowtype;
    t_emp_id bulk_emp_id := bulk_emp_id();
    cursor c_emp is
    select * from employee;
begin
    open c_emp;
    loop
    fetch c_emp
    bulk collect into t_emp_id limit 1000;
    exit when t_emp_id.count=0;
    forall i in t_emp_id.first..t_emp_id.last
        update employee  set status=2 where emp_id=t_emp_id(i);
    COMMIT;
    DBMS_OUTPUT.put_line(t_emp_id.count || ' rows');
    end loop;
    close c_emp;
end;
/

I am getting an error at 

update employee  set status=2 where emp_id=t_emp_id.emp_id(i);

PLS-00382: expression is of wrong type

the code works fine for just the BULK COLLECT.

Upvotes: 0

Views: 84

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

t_emp_id is a collection of RECORD. You need to get the value of the column at ith index. Change this statement 

update employee  set status=2 where emp_id=t_emp_id(i);

to

update employee  set status=2 where emp_id=t_emp_id(i).emp_id;

Upvotes: 4

Related Questions