Bulk Binding in Apex

Just a reminder for all those folks that just because we're saving ourselves network latency by working with Oracle Application Express, it doesn't mean we can forget some of the other nifty features of the Oracle Database.

Take working with check-boxes in tabular forms. Evil has that concept is, we can still eke out some performance benefits by using bulk binding.

In the relevant Advanced Tutorial in the Apex documentation, and in many other examples around OTN, presentations, blogs etc, we see a PL/SQL block such as this:

FOR i in 1..APEX_APPLICATION.G_F01.count
LOOP
   DELETE FROM demo_product_info
   WHERE product_id = APEX_APPLICATION.G_F01(i);
END LOOP;

With a minor tweak, we can convert this to

FORALL i IN INDICES OF APEX_APPLICATION.G_F01
   DELETE FROM demo_product_info
   WHERE product_id = APEX_APPLICATION.G_F01(i);

Benefits of which will accumulate within your database, better habits will form, and your DBAs will be happier. Further information on the benefits may be found here.

That is all :-)