Monday 22 February 2010

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 :-)

4 comments:

Jeffrey Kemp said...

Don't forget to remove the END LOOP ;)

Scott Wesley said...

Argh, I knew I should have just copied my previous example instead of re-creating! Cheers!

Stew Ashton said...

Bravo for this! I think there should be more "SQL Developer" reminders on APEX blogs, because there are some bad examples out there. One blogger was putting SQL into his Javascript, which is the very definition of SQL injection. No one seems to be using "Web pagination" (search for this on asktom.oracle.com for details). Everyone seems to count the number of rows available even though it uses up a lot of resources for no real return.

Scott Wesley said...

Cheers, you raise a good point about the pagination. I remember Tom really jumping on this a fair bit a few years ago, but I haven't seen it talked about as much recently. It's all entries about new features - which I can't complain about. I'll have to keep a look out for other simple examples I stumble across.