HI ,
I am using bulk collect for fetching the data and forall for updating the records thats working fine please find below code
create table emp_test
(
employee_id number,
ename varchar2(100),
deptno number,
salary number,
CONSTRAINT emp_pk PRIMARY KEY(employee_id)
);
insert into emp_test(employee_id,ename,deptno,salary) values
(1,'reema',1,1200);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(2,'sneha',1,1000);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(3,'vikas',1,4000);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(4,'jiten',1,3000);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(5,'kiran',1,500);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(6,'ashish',2,600);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(7,'pavan',2,700);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(8,'techm',2,800);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(9,'hsbc',2,6789);
/
insert into emp_test(employee_id,ename,deptno,salary) values
(10,'agilets',2,467);
/
create or replace procedure increase_sal(
p_deptno in emp.deptno%type,
p_incre in number)
as
TYPE l_emp1 IS TABLE OF EMP_test.employee_id%type index by pls_integer;
l_emp l_emp1;
begin
select employee_id BULK COLLECT
into l_emp from emp_test
where deptno=p_deptno;
FORALL i in 1..l_emp.count
update emp_TEST
set salary =salary +p_incre
where employee_id=l_emp(i);
end increase_sal;
begin
increase_sal(p_deptno =>1,p_incre=>100);
end;
i want to return the exceptionsfrom procedure of forall functionality of SQL%BULK_EXCEPTIONS.ERROR_INDEX AND SQL%BULK_EXCEPTIONS.ERROR_CODE
for example if any of the record is missing in deptno no so it will not be able to update can anybody help
create or replace procedure increase_sal(
p_deptno in emp.deptno%type,
p_incre in number)
as
TYPE l_emp1 IS TABLE OF EMP_test.employee_id%type index by pls_integer;
l_emp l_emp1;
BULK_ERROR EXCEPTION ;
PRAGMA INIT_EXCEPTION(BULK_ERROR,-24381);
begin
select employee_id BULK COLLECT
into l_emp from emp_test
where deptno=p_deptno;
FORALL i in 1..l_emp.count
SAVE EXCEPTIONS
update emp_TEST
set salary =salary +p_incre
where employee_id=l_emp(i);
EXCEPTION WHEN BULK_ERROR THEN
FOR J IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('ERROR AT '||SQL%BUL_EXCEPTIONS(J).ERROR_CODE);
DBMS_OUTPUT.PUT_LINE('ERROR MSG'||SQL%BULK_EXCEPTIONS(J).ERROR_CODE);
END LOOP;
end increase_sal;