Build your APEX application better – do less in APEX

I recently saw this approach used in a complex Apex application built for my current client, and I liked what I saw – so I used a similar one in another project of mine, with good results.

  1. Pages load and process faster
  2. Less PL/SQL compilation at runtime
  3. Code is more maintainable and reusable
  4. Database object dependency analysis is much more reliable
  5. Apex application export files are smaller – faster to deploy
  6. Apex pages can be copied and adapted (e.g. for different interfaces) easier

ratsnest-app
How did all this happen? Nothing earth-shattering or terribly original. I made the following simple changes – and they only took about a week for a moderately complex 100-page application (that had been built haphazardly over the period of a few years):

  1. All PL/SQL Process actions moved to database packages
  2. Each page only has a single Before Header Process, which calls a procedure (e.g. CTRL_PKG.p1_load;)
  3. Each page only has a single Processing Process, which calls a procedure (e.g. CTRL_PKG.p1_process;)
  4. Computations are all removed, they are now done in the database package

The only changes I needed to make to the PL/SQL to make it work in a database package were that bind variable references (e.g. :P1_CUSTOMER_NAME) needed to be changed to use the V() (for strings and dates) or NV() (for numbers) functions; and I had to convert the Conditions on the Processes into the equivalent logic in PL/SQL. Generally, I would retrieve the values of page items into a local variable before using it in a query.

My “p1_load” procedure typically looked something like this:

PROCEDURE p1_load IS
BEGIN
  msg('p1_load');

  member_load;

  msg('p1_load Finished');
END p1_load;

My “p1_process” procedure typically looked something like this:

PROCEDURE p1_process IS
  request VARCHAR2(100) := APEX_APPLICATION.g_request;
BEGIN
  msg('p1_process ' || request);

  CASE request
  WHEN 'CREATE' THEN

    member_insert;

  WHEN 'SUBMIT' THEN

    member_update;

  WHEN 'DELETE' THEN

    member_delete;

    APEX_UTIL.clear_page_cache
      (APEX_APPLICATION.g_flow_step_id);

  WHEN 'COPY' THEN

    member_update;

    -- clear the member ID for a new record
    sv('P1_MEMBER_ID');

  ELSE
    NULL;
  END CASE;

  msg('p1_process Finished');
END p1_process;

I left Validations and Branches in the application. I will come back to the Validations later – this is made easier in Apex 4.1 which provides an API for error messages.

It wasn’t until I went through this exercise that I realised what a great volume of PL/SQL logic I had in my application – and that PL/SQL was being dynamically compiled every time a page was loaded or processed. Moving it to the database meant that it was compiled once; it meant that I could more easily see duplicated code (and therefore modularise it so that the same routine would now be called from multiple pages). I found a number of places where the Apex application was forced to re-evaluate a condition multiple times (as it had been copied to multiple Processes on the page) – now, all those processes could be put together into one IF .. END IF block.

Once all that code is compiled on the database, I can now make a change to a schema object (e.g. drop a column from a table, or modify a view definition) and see immediately what impact it will have across the application. No more time bombs waiting to go off in the middle of a customer demo. I can also query ALL_DEPENDENCIES to see where an object is being used.

I then wanted to make a Mobile version of a set of seven pages. This was made much easier now – all I had to do was copy the pages, set their interface to Mobile, and then on the database, call the same procedures. Note that when you do a page copy, that Apex automatically updates all references to use the new page ID – e.g. if you copy Page 1 to Page 2, a Process that calls “CTRL_PKG.p1_load;” will be changed to call “CTRL_PKG.p2_load;” in the new page. This required no further work since my p1_load and p1_process procedures merely had a one-line call to another procedure, which used the APEX_APPLICATION.g_flow_step_id global to determine the page number when using page items. For example:

PROCEDURE member_load IS
  p VARCHAR2(10) := 'P' || APEX_APPLICATION.g_flow_step_id;
  member members%ROWTYPE;
BEGIN
  msg('member_load ' || p);  

  member.member_id := nv(p || '_MEMBER_ID');  
  msg('member_id=' || member.member_id);  

  IF member.member_id IS NOT NULL THEN    

    SELECT *
    INTO   member_page_load.member
    FROM   members m
    WHERE  m.member_id = member_load.member.member_id;    

    sv(p || '_GIVEN_NAME',        member.given_name);
    sv(p || '_SURNAME',           member.surname);
    sv(p || '_SEX',               member.sex);
    sv(p || '_ADDRESS_LINE',      member.address_line);
    sv(p || '_STATE',             member.state);
    sv(p || '_SUBURB',            member.suburb);
    sv(p || '_POSTCODE',          member.postcode);
    sv(p || '_HOME_PHONE',        member.home_phone);
    sv(p || '_MOBILE_PHONE',      member.mobile_phone);
    sv(p || '_EMAIL_ADDRESS',     member.email_address);
    sv(p || '_VERSION_ID',        member.version_id);

  END IF; 

  msg('member_load Finished');
END member_load;

Aside: Note here the use of SELECT * INTO [rowtype-variable]. This is IMO the one exception to the “never SELECT *” rule of thumb. The compromise here is that the procedure will query the entire record every time, even if it doesn’t use some of the columns; however, this pattern makes the code leaner and more easily understood; also, I usually need almost all the columns anyway.

In my database package, I included the following helper functions at the top, and used them throughout the package:

DATE_FORMAT CONSTANT VARCHAR2(30) := 'DD-Mon-YYYY';

PROCEDURE msg (i_msg IN VARCHAR2) IS
BEGIN
  APEX_DEBUG_MESSAGE.LOG_MESSAGE
    ($$PLSQL_UNIT || ': ' || i_msg);
END msg;

-- get date value
FUNCTION dv
  (i_name IN VARCHAR2
  ,i_fmt IN VARCHAR2 := DATE_FORMAT
  ) RETURN DATE IS
BEGIN
  RETURN TO_DATE(v(i_name), i_fmt);
END dv;

-- set value
PROCEDURE sv
  (i_name IN VARCHAR2
  ,i_value IN VARCHAR2 := NULL
  ) IS
BEGIN
  APEX_UTIL.set_session_state(i_name, i_value);
END sv;

-- set date
PROCEDURE sd
  (i_name IN VARCHAR2
  ,i_value IN DATE := NULL
  ,i_fmt IN VARCHAR2 := DATE_FORMAT
  ) IS
BEGIN
  APEX_UTIL.set_session_state
    (i_name, TO_CHAR(i_value, i_fmt));
END sd;

PROCEDURE success (i_msg IN VARCHAR2) IS
BEGIN
  msg('success: ' || i_msg);
  IF apex_application.g_print_success_message IS NOT NULL THEN
    apex_application.g_print_success_message :=
      := apex_application.g_print_success_message || '<br>';
  END IF;
  apex_application.g_print_success_message
    := apex_application.g_print_success_message || i_msg;
END success;

Another change I made was to move most of the logic embedded in report queries into views on the database. This led to more efficiencies as logic used in a few pages here and there could now be consolidated in a single view.

The challenges remaining were record view/edit pages generated by the Apex wizard – these used DML processes to load and insert/update/delete records. In most cases these were on simple pages with no other processing added; so I left them alone for now.

On a particularly complex page, I removed the DML processes and replaced them with my own package procedure which did the query, insert, update and delete. This greatly simplified things because I now had better control over exactly how these operations are done. The only downside to this approach is that I lose the built-in Apex lost update protection mechanism, which detects changes to a record done by multiple concurrent sessions. I had to ensure I built that logic into my package myself – I did this with a simple VERSION_ID column on the table (c.f. Version Compare in “Avoiding Lost Updates”).

The only downsides with this approach I’ve noted so far are:

  1. a little extra work when initially creating a page
  2. page item references are now strings (e.g. “v('P1_RECORD_ID')“)  instead of bind variables – so a typo here and there can result in somewhat harder-to-find bugs

However, my application is now faster, more efficient, and on the whole easier to debug and maintain – so the benefits seem to outweigh the downsides.

Jeffrey Kemp 13 February 2014

APEX / apex-4.2 / best-practice / TAPI / XAPI /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Change an item Label dynamically
APEX_UTIL.set_session_state may or may not commit

Comments

  1. stewashton
    13 February 2014 - 7:37 pm

    Good post that shows how good or bad APEX application code can be. APEX is no different from any other application framework: keeping presentation and data processing layers separate is a good thing.

    One warning about the SELECT * INTO: with Oracle 12c “invisible columns”, the “*” may not return all the columns. I suggest using what Tom Kyte calls a “cursor template”. Define a cursor cur_member that selects the columns you want, then use cur_member%ROWTYPE instead of members%ROWTYPE. You don’t have to use the cursor for anything else. You get the advantages of ROWTYPE while choosing explicitly the columns you want.

  2. Christian Neumueller
    13 February 2014 - 10:43 pm

    It’s definitely a good practice to move logic into views and packages and I hope that we can simplify this some more in future APEX versions. For example, recently I discussed possible refactoring support in the code editor with Patrick Wolf.

    Your solution reminds me of something similar from a few years ago, before joining Oracle. We implemented extensions to a Forms based ERP system with APEX. The applications had one app level process for each process point (before header, submit, etc). The callout was to a dispatcher procedure that delegated the call further, via naming conventions and a lookup using ALL_PROCEDURES. For example, the before header dispatcher looked first for a procedure “ctrl__.before_header”, then “ctrl_.before_header” and finally “ctrl_generic.before_header”. It called the procedures it found outside in or inside out, depending on the process type. The submit dispatcher also looked for “on_” procedures. There was much more to this solution (like a language extension to PL/SQL), but that is off topic.

    A slight disadvantage of such a solution is that you lose declarative features of APEX. For example, authorization checks have to be implemented in the procedure that implements a process, instead of enabling the right authorization at component level. This also makes the job of the Advisor and similar tools a bit harder.

    • Jeffrey Kemp
      13 February 2014 - 11:26 pm

      Hi Christian,

      You’ve now reminded me of the generated PLL code that I used to work with in Forms – each trigger (when-new-form-instance, when-validate-item, etc) would call a PLL procedure with logic for that form, and then call an application-wide PLL with logic that would apply across the whole application.

      You got me thinking now about Apex and how its declarative features could still be used in a schema like this. Mind you, with regard to authorizations, I generally only find these being used on pages, regions and items; rarely on processes. And then, my authorizations tend to be based on the result of a single function call anyway, so it’s easy enough to call the same function in my database-level PL/SQL code anyway.

      Just a wild idea, that might subvert this whole thing – how about an option where an Apex page can have a setting called “Implemented via Database Package”, which causes the page to look for all its processes and validations in a specified database package. The package would be required to supply procedures/functions that match a signature expected by Apex (just as we do now with authentication schemes). But, and this is the cool bit, the code in the database package is exposed in the Apex page builder just like processes are now – and they are editable – and when you save, it actually recompiles the database package with the new code. As a developer, I can edit the logic for the page using Apex, or directly on the database using SQL Developer or whatever.

      What do you think?

      • Christian Neumueller
        13 February 2014 - 11:49 pm

        Hi Jeffrey,

        regarding authorizations, you for example need to synchronize these between submit buttons and the processes that implement them. If only the buttons have authorizations, somebody could manipulate the submit request and still call the process, even though the button is not shown. The Advisor warns you when it sees a mismatch between authorizations on buttons and related processes.

        Your “Implemented via Database Package” idea is convenient for somebody who likes to work this way and could be implemented more efficiently in the APEX engine. I surely would have liked such a solution in that project I mentioned. The editing idea is also interesting. Maybe you should add a feature request on http://apex.oracle.com/vote, where more people can discuss it!

      • Scott
        3 March 2014 - 9:19 pm

        Reading this made me think of the same project (I think I know which one you mean)

        I think this or a variation thereof would be helpful in encouraging developers to move their APEX PL/SQL to packages – so many advantages

  3. Christian Neumueller
    13 February 2014 - 10:49 pm

    That should have been
    …
    For example, the before header dispatcher looked first for a procedure “ctrl_[app alias]_[page id].before_header”, then “ctrl_[app alias].before_header” and finally “ctrl_generic.before_header”. It called the procedures it found outside in or inside out, depending on the process type. The submit dispatcher also looked for “on_[request]” procedures
    …

  4. Jeffrey Kemp
    14 February 2014 - 12:08 am

    Vote for the Apex enhancement request! Feature Request AJJ6

  5. Martin D’Souza
    14 February 2014 - 12:36 am

    Good article and I use similar practices in my applications. Some things to keep in mind is that all references to V() and NV() hit tables (SQL). Also the apex_util.set_session_states does implicit commits: http://www.danielmcghan.us/2012/08/implicit-commits-in-apex.html

    Usually what I recommend to clients is that small things to into APEX directly. Larger things go into PL/SQL / Views. It’s all on a case-by-case basis.

    • Jeffrey Kemp
      14 February 2014 - 12:42 am

      Wow, I didn’t know that! set_session_state issues a commit… that puts a bit of a dampener on things.

      I’m starting to think it might be a good idea to pass all the page items via procedure parameters – IN/OUT – which would preserve the desired transactional nature of my process.

  6. Fateh Al-Msaddi
    21 February 2014 - 1:42 am

    Hello,
    Why do not you use Package on tables TAPI ( in sql workshop) To avoid Lost update ?

    Regards,

    • Jeffrey Kemp
      21 February 2014 - 8:21 am

      Hi Fateh,

      I don’t have any particular problem with auto-generated TAPIs but I haven’t used them extensively, preferring to hand-code my APIs myself.

  7. Nikola
    24 September 2017 - 5:18 am

    Hi, this is great article. I’m trying for a long time to switch application to packages, but didn’t find any good examples. This is great example when you use Page Items. But what about Tabular Form ( i mean manual written tabular forms)?
    Do you have example how to create Packaged PL/SQL Process for example updating table with data from Tabular Form?

    This is code that i use as APEX Page process that update table:

    begin

    FOR i IN 1 .. apex_application.g_f08.COUNT

    LOOP

    UPDATE EMP
SET ENAME = apex_application.g_f01(i)
WHERE EMPNO = apex_application.g_f08 (i);

    END LOOP;

    END;

    So, how put that in PL/SQL Package?

  8. Joe Nard
    26 April 2019 - 2:49 pm

    Hi Jeffrey,

    Do you have example for insert procedure for this?

    Thanks,

    • Jeffrey Kemp
      26 April 2019 - 2:59 pm

      Hi Joe, the TAPI uses just an ordinary insert statement – what exactly are you after? Jeff

  9. Joe Nard
    29 April 2019 - 2:04 pm

    Hi Jeffrey,

    So this uses a simple insert statement.

    Thanks,

  10. Kjell Andersson
    22 January 2020 - 6:08 pm

    Hi Jeffrey, i like your article

    I have a question about IR (Interactive report) i am using APEX 5.0 and are there any solution to replace the SQL query in APEX to a package?

    Kjell

  11. Kjell Andersson
    22 January 2020 - 10:35 pm

    Hi Jeffrey, i will try this

    Thanks, Kjell

  12. Mike McKenna
    5 September 2021 - 5:05 am

    I know this is an old article, but as someone new to APEX, I always come back to refer to this article. I wish there were more articles that went into depth of APEX best practices like this. I’ve tried to put most of my logic in database packages, but has taken a lot of effort to figure out how to wire things up at first.

    Would love an updated blog post to provide examples that also include Validations and Branches, as well as, any changes you have made over time with the latest API/version of APEX and gained knowledge. Thanks again for the article.

    • Jeffrey Kemp
      6 September 2021 - 7:55 am

      Hi Mike,

      Thank you for your note – it’s always nice to hear when people appreciate these articles.

      You are absolutely right that things have moved on since this article was posted. I would say this was “what worked for me” and is not necessarily going to be “Best Practice” for all situations.

      There have been projects where I have avoided this approach and preferred a lowest-of-low-code solution, but these were projects with a low volume of PL/SQL logic anyway and they were able to take advantage of APEX’s built-in declarative features for the most part.

      Nowadays, for complex applications requiring a good amount of database logic, I still make sure to move as much of this logic to database packages as possible, but the method by which data is passed to-and-from the APEX session state is different now – with the new Form region’s process, for example, there is no need to use a whole lot of “sv()” calls to read session state. I’d like to write an article on this some day.

      With regards to Validations and Branches, my preference is still to keep them declarative – use APEX Validation and Branches as they were intended. Future developers will thank you. However, I do find there are a minority of pages that deal with complex business process that require a lot of complex validations – in these cases, I will choose to use a single “val()” PL/SQL-based validation process; this process would then call the APEX Errors API to raise validation messages.

      For Branches, I haven’t changed my approach that much. If a page needs to conditionally branch, I’ll create branches for each target with a condition to determine which one takes precedence. This may require a hidden item on the page to remember the result of some evaluation in the page process upon which a condition relies (tip: set the Source on the hidden item to “null” and set the attribute “Used” to “Always – replace any existing value in session state” so that it won’t remember the value on subsequent page loads).

      I hope this helps.

      Jeff

  13. Mike McKenna
    7 September 2021 - 7:01 pm

    Appreciate the response Jeff. Yeah we are in the process of porting over all our mod_plsql applications, and they have a lot of validation code that is just easier to do in a database package. Still learning my around APEX and appreciate all your efforts and knowledge on this blog. It has helped me get going in the right direction, but I still have much to learn.

Leave a Reply

Your email address will not be published / Required fields are marked *