Friday, March 06, 2015

The Ideal APEX Application (When & Where You Write Code)

The real title of this post should be "What I Really Meant to Say Was....".

Bob Rhubart of the Oracle Technology Network OTNArchBeat fame was kind enough to give me an opportunity to shoot a 2-minute Tech Tip.  I love Bob's goals for a 2-minute Tech Tip - has to be technical, can't be marketing fluff, and you have to deliver it in 120 seconds - no more, no less.  So I took some notes, practiced it out loud a couple times, and then I was ready.  But because I didn't want to sound like I was merely reading my notes, I ad-libbed a little and...crumbled under the clock.  I don't think I could have been more confusing and off the mark.  Oh...did I forget to mention that Bob doesn't like to do more than one take?

So if I could distill what I wished to convey into a few easily consumable points:
  1. Use the declarative features of APEX as much as possible, don't write code.  If you have to choose between writing something in a report region with a new template, or hammer out the same result with a lovingly hand-crafted PL/SQL region, opt for the former.  If you have a choice between a declarative condition (e.g., Item Not Null) or the equivalent PL/SQL expression, choose the declarative condition.  It will be faster at execution time, it will be easier to manage and report upon, it will be easier to maintain, it will be less pressure on your database with less parsing of PL/SQL.
  2. When you need to venture outside the declarative features of APEX and you need to write code in PL/SQL, be smart about it.  Define as much PL/SQL in statically compiled units (procedures, functions, packages) in the database and simply invoke them from your APEX application.  It will be easier to maintain (because it will simply be files that correspond to your PL/SQL procedures/functions/packages), it will be easier to version control, it will be easier to diff and promote, you can choose which PL/SQL optimization level you wish, you can natively compile, and it will be much more efficient on your database.
  3. Avoid huge sections of JavaScript and use Dynamic Actions wherever possible.  If you have the need for a lot of custom JavaScript, put it into a library and into a file, served by your Web Server (or, at a minimum, as a shared static file of your application).
  4. APEX is just a thin veneer over your database - architect your APEX applications as such.  Let the Oracle Database do the heavy lifting.  Your APEX application definition should have very little code. It should be primarily comprised of SQL queries and simple invocations of your underlying PL/SQL programs.

My rule of thumb - when you're editing code in a text area/code editor in the Application Builder of APEX and you see the scroll bar, it's time to consider putting it into a PL/SQL package.  And of course, if you catch yourself writing the same PL/SQL logic a second time, you should also consider putting it into a PL/SQL package.

There's more to come from the Oracle APEX team on @OTNArchBeat.