WP Remix
Learning and Technology never Cease to Change
21
May

Exploit the Bulk Bind of PL/SQL

Posted by Hariharan Ragunathan

After a long time, let me write a bit of Oracle this week. Improve the performance of multi-row SQL operations with bulk/array processing in PL/SQL. For code snippets of the form


cursor emp_cur is
select employee_id
from employee where department_id = dept_in;

begin
for rec in emp_cur loop
update employee set salary = salary + 1000
where employee_id = rec.employee_id;
end loop;
end;

Bulk processing of PL/SQL can be used to improve its performance to greater levels. Use the FORALL and BULKCOLLECT of PL/SQL that will scale the application. The code snippet can be changed to

select employee_id
bulk collect into collection_var
from employee where department_id = dept_in;

begin
forall i in collection_var.first .. collection_var.last
 update employee set salary = salary + 1000
where employee_id = collection_var(i);
end;

The context switch happening in executing PL/SQL and SQL code is reduced in this. The Save Exceptions and SQL%BULK_EXCEPTION is another good introduction on excellent exception handling on these bulk processing.
Category : Programming

No comments yet.

Leave a comment


 HARIHARAN RAGUNATHAN
 Hyderabad, India


This is a place to express my opinions on things which I stumble on in my day to day life. The views which I have expressed are my own opinions and not meant to disparage anything or any one.