Sunday, August 30, 2015

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

If you are getting the below error while executing a DML statement in PLSQL Script.
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

This means you might have forgotten the identifier i (as in my case) to include inside forall loop.

Here below is a script which I was trying when I found this error.
declare
type emp_no is table of emp.empno%type;
type emp_name is table of emp.ename%type;
type emp_job is table of emp.job%type;
type emp_sal is table of emp.sal%type;

v_empno emp_no;
v_ename emp_name;
v_job emp_job;
v_sal emp_sal;
cursor c1 is select empno, ename, job, sal from emp;
BEGIN
open c1;
fetch c1 bulk collect into v_empno, v_ename, v_job, v_sal limit 200;
close c1;
forall i IN v_empno.first .. v_empno.last
    update emp set sal = v_sal*1.02 where job = 'CLERK';
    end;
    /
   

Error at line 1
ORA-06550: line 17, column 26:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 17, column 5:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

to correct this error.
include the loop identifier (i) with the local collection type variable

declare
type emp_no is table of emp.empno%type;
type emp_name is table of emp.ename%type;
type emp_job is table of emp.job%type;
type emp_sal is table of emp.sal%type;

v_empno emp_no;
v_ename emp_name;
v_job emp_job;
v_sal emp_sal;
cursor c1 is select empno, ename, job, sal from emp;
BEGIN
open c1;
fetch c1 bulk collect into v_empno, v_ename, v_job, v_sal limit 200;
close c1;
forall i IN v_empno.first .. v_empno.last
    update emp set sal = v_sal(i)*1.02 where job = 'CLERK';
    end;
    /
   


   
    

No comments:

Post a Comment