Hi Masters,
I am getting the below error.. please advise..!!
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL;
CREATE OR REPLACE procedure DUMMY_USER.update_departments as
type l_department_id is table of number(4) index by binary_integer;
type l_manager_id is table of number(9) index by binary_integer;
ldepartment_id l_department_id;
lmanager_id l_manager_id;
cursor v_cur is select department_id,manager_id from departments_a;
errors pls_integer;
vsession_id varchar2(50):= sys_context('userenv','sessionid');
begin
open v_cur;
loop
fetch v_cur bulk collect into ldepartment_id,lmanager_id limit 1000;
forall i in 1..ldepartment_id.count save exceptions
update /*+ PARALLEL(departments_b, 8) */ departments_b set manager_id=rownum where session_id=vsession_id;
exit when v_cur%notfound;
end loop;
commit;
dbms_output.put_line('Total no.of records are updated=' || v_cur%rowcount);
close v_cur;
Exception
when others then
errors:=sql%bulk_exceptions.count;
dbms_output.put_line('No.of Errors='||errors);
for i in 1..errors
loop
dbms_output.put_line('Errors #'||i||'at'||'iteration#'||sql%bulk_exceptions(i).error_index);
dbms_output.put_line('Errors message is'||sql%bulk_exceptions(i).error_code);
end loop;
end update_departments;
Regards
Ar