Skip to content

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;
    sv('P1_MEMBER_ID'); -- clear the member ID for a new record
  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.

Change an item Label dynamically

Get it? “an item with many hats”… yeah ok.

Need to change the label of an item on-the-fly? When I run my Apex page it renders item labels like this:

<label for="P1_CONTACT_NUMBER">
  <span>Contact Number</span>
</label>

If the label needs to change based on another item, I could set the label with the value of another item, e.g. “&P1_CONTACT_NUMBER_LABEL.” and when the page is refreshed it would pick up the new label. But at runtime, if the label needs to change dynamically in response to changes in other items, we need to do something else.

Caveat: The need for changing the label should be very rare – it’s bad practice to overload one field with multiple meanings. But if you must, this is what you can do.

It’s easy with a Dynamic Action running some Javascript. This changes the label text for the P1_CONTACT_NUMBER item depending on the value chosen for P1_CONTACT_METHOD, which might be a radio group or select list. The method uses jquery to search for a “label” tag with the attribute “for” that associates it with the desired item; we then navigate down to the “span” element, and call the “text” function to change the label text:

if ($v("P1_CONTACT_METHOD")=='SMS') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Mobile")
} else if ($v("P1_CONTACT_METHOD")=='EMAIL') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Email")
} else {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Number")
}

The Dynamic Action is set up as follows:

Event = Change
Selection Type = Item(s)
Item(s) = P1_CONTACT_METHOD
Condition = (no condition)

True Action = Execute JavaScript Code
Fire On Page Load = Yes
Selection Type = (blank)
Code = (the javascript shown above)

Parallel Development in Apex

Source: http://paulhammant.com/files/multi-branch.jpgMy current client has a large number of Apex applications, one of which is a doozy. It is a mission-critical and complex application in Apex 4.0.2 used throughout the business, with an impressively long list of features, with an equally impressively long list of enhancement requests in the queue.

They always have a number of projects on the go with it, and they wanted us to develop two major revisions to it in parallel. In other words, we’d have v1.0 (so to speak) in Production, which still needed support and urgent defect fixing, v1.1 in Dev1 for project A, and v1.2 in Dev2 for project B. Oh, and we don’t know if Project A will go live before Project B, or vice versa. We have source control, so we should be able to branch the application and have separate teams working on each branch, right?

We said, “no way”. Trying to merge changes from a branch of an Apex app into an existing Apex app is not going to work, practically speaking. The merged script would most likely fail to run at all, or if it somehow magically runs, it’d probably break something.

So we pushed back a bit, and the terms of the project were changed so that development of project A would be done first, and the development of project B would follow straight after. So at least now we know that v1.2 can be built on top of v1.1 with no merge required. However, we still had the problem that production defect fixes would still need to be done on a separate version of the application in dev, and that they needed to continue being deployed to sit/uat/prod without carrying any changes from our projects.

The solution we have used is to have two copies of dev, each with its own schema, apex application and version control folder: I’ll call them APP and APP2. We took an export of APP and created APP2, and instructed the developer who was tasked with production defect fixes to manually duplicate his changes in both APP and APP2. That way the defect fixes were “merged” in a manual fashion as we went along – also, it meant that the project development would gain the benefit of the defect fixes straight away. The downside was that everything worked and acted as if they were two completely different and separate applications, which made things tricky for integration.

Next, for developing project A and project B, we needed to be able to make changes for both projects in parallel, but we needed to be able to deploy just Project A to SIT/UAT/PROD without carrying the changes from project B with it. The solution was to use Apex’s Build Option feature (which has been around for donkey’s years but I never had a use for it until now), in combination with Conditional Compilation on the database schema.

I created a build option called (e.g.) “Project B”. I set Status = “Include”, and Default on Export = “Exclude”. What this means is that in dev, my Project B changes will be enabled, but when the app is exported for deployment to SIT etc the build option’s status will be set to “Exclude”. In fact, my changes will be included in the export script, but they just won’t be rendered in the target environments.

When we created a new page, region, item, process, condition, or dynamic action for project B, we would mark it with our build option “Project B”. If an existing element was to be removed or replaced by Project B, we would mark it as “{NOT} Project B”.

Any code on the database side that was only for project B would be switched on with conditional compilation, e.g.:

$IF $$projectB $THEN
  PROCEDURE my_proc (new_param IN ...) IS...
$ELSE
  PROCEDURE my_proc IS...
$END

When the code is compiled, if the projectB flag has been set (e.g. with ALTER SESSION SET PLSQL_CCFLAGS='projectB:TRUE';), the new code will be compiled.

Build Options can be applied to:

  • Pages & Regions
  • Items & Buttons
  • Branches, Computations & Processes
  • Lists & List Entries
  • LOV Entries
  • Navigation Bar & Breadcrumb Entries
  • Shortcuts
  • Tabs & Parent Tabs

This works quite well for 90% of the changes required. Unfortunately it doesn’t handle the following scenarios:

1. Changed attributes for existing Apex components – e.g. some layout changes that would re-order the items in a form cannot be isolated to a build option.

2. Templates and Authorization Schemes cannot be marked with a build option.

On the database side, it is possible to detect at runtime if a build option has been enabled or not. In our case, a lot of our code was dependent on schema structural changes (e.g. new table columns) which would not compile in the target environments anyway – so conditional compilation was a better solution.

Apart from these caveats, the use of Build Options and Conditional Compilation have made the parallel development of these two projects feasible. Not perfect, mind you – but feasible. The best part? There’s a feature in Apex that allows you to view a list of all the components that have been marked with a Build Option – this is accessible from Shared Components -> Build Options -> Utilization (tab).

Enhancement Requests:

1. If Build Options could be improved to allow the scenarios listed above, I’d be glad. In a perfect world, I should be able to go into Apex, select “Project B”, and all my changes (adding/modifying/removing items, regions, pages, LOVs, auth schemes, etc) would be marked for Project B. I could switch to “Project A”, and my changes for Project B would be hidden. I think this would require the Apex engine to be able to have multiple definitions of each item, region or page, one for each build option. Merging changes between build options would need to be made possible, somehow – I don’t hold any illusions that this would be a simple feature for the Apex team to deliver.

2. Make the items/regions/pages listed in the Utilization tab clickable, so I can easily click through and change properties on them.

3. Another thing I’d like to see from the Apex team is builtin GUI support for exporting applications as a collection of individual scripts, each independently runnable – one for each page and shared component. I’m aware there is a Java tool for this purpose, but the individual scripts it generates cannot be run on their own. For example, if I export a page, I should be able to import that page into another copy of the same application (but with a different application ID) to replace the existing version of that page. I should be able to check in a change to an authorization scheme or an LOV or a template, and deploy just the script for that component to other applications, even in other workspaces. The export feature for all this should be available and supported using a PL/SQL API so that we can automate the whole thing and integrate it with our version control and deployment software.

4. What would be really cool, would be if the export scripts from Apex were structured in such a way that existing source code merge tools could merge different versions of the same Apex script and result in a usable Apex script. This already works quite well for our schema scripts (table scripts, views, packages, etc), so why not?

Further Reading:

Fixing phone numbers

An enhancement request I was assigned was worded thus:

“User will optionally enter the Phone number (IF the phone was blank the system will default the store’s area code).”

I interpret this to mean that the Customer Phone number (land line) field should remain optional, but if entered, it should check if the local area code had been entered, and if not, default it according to the local store’s area code. We can assume that the area code has already been entered if the phone number starts with a zero (0).

This is for a retail chain with stores throughout Australia and New Zealand, and the Apex session knows the operator’s store ID. I can look up the country code and phone number for their store with a simple query, which will return values such as (these are just made up examples):

Country AU, Phone: +61 8 9123 4567 – area code should be 08
Country AU, Phone: 08 91234567 – area code should be 08
Country AU, Phone: +61 2 12345678 – area code should be 02
Country AU, Phone: 0408 123 456 – no landline area code
Country NZ, Phone: +64 3 123456 – area code should be 03
Country NZ, Phone: 0423 456 121 – area code should be 04

They only want to default the area code for landlines, so if the store’s phone number happens to be a mobile phone number it should not do any defaulting.

Step 1: create a database function (in a database package, natch) to return the landline area code for any given store ID.

FUNCTION get_store_landline_area_code (p_store_id IN VARCHAR2) RETURN VARCHAR2 IS
  v_area_code VARCHAR2(2);
  v_country_code stores_vw.country_code%TYPE;
  v_telephone_number stores_vw.telephone_number%TYPE;
BEGIN
  IF p_store_code IS NOT NULL THEN

    BEGIN

      SELECT country_code
            ,telephone_number
      INTO   v_country_code
            ,v_telephone_number
      FROM   stores_vw
      WHERE  store_id = p_store_id;

      v_area_code
        := CASE
           -- Australian International land line
           WHEN p_country_code = 'AU'
           AND REGEXP_LIKE(p_telephone_number, '^\+61( ?)[2378]')
             --e.g. +61 8 9752 6100
             THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1)
           -- Australian Local land line
           WHEN p_country_code = 'AU'
           AND REGEXP_LIKE(p_telephone_number, '^0[2378]')
             THEN SUBSTR(p_telephone_number, 1, 2)
           -- New Zealand International land line
           WHEN p_country_code = 'NZ'
           AND REGEXP_LIKE(p_telephone_number, '^\+64( ?)[34679]')
             -- e.g. +64 3 1234 567
             THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1)
           -- New Zealand Local land line
           WHEN p_country_code = 'NZ'
           AND REGEXP_LIKE(p_telephone_number, '^0[34679]')
             THEN SUBSTR(p_telephone_number, 1, 2)
           ELSE
             NULL
           END;

    EXCEPTION
      WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
        NULL;
    END;

  END IF;
  RETURN v_area_code;
END get_store_landline_area_code;

Phone number references:
http://en.wikipedia.org/wiki/Telephone_numbers_in_Australia
http://en.wikipedia.org/wiki/Telephone_numbers_in_New_Zealand

Step 2: add a Dynamic Action to prepend the area code to the phone number, if it wasn’t entered already:

Event: Change
Selection Type: Item(s)
Item(s): P1_CUSTOMER_PHONE_NUMBER
Condition: Javascript expression
Value: $v("P1_CUSTOMER_PHONE_NUMBER").length > 0 && $v("P1_CUSTOMER_PHONE_NUMBER").charAt(0) != "0"
True Action: Set Value
Set Type: PL/SQL Expression
PL/SQL Expression: my_util_pkg.get_store_landline_area_code(:F_USER_STORE_ID) || :P1_CUSTOMER_PHONE_NUMBER

Now, when the user types in a local land line but forget the prefix, the system will automatically add it in as soon as they tab out of the field. If the phone number field is unchanged, or is left blank, this will do nothing.

It assumes that the customer’s phone number uses the same prefix as the store, which in most cases will be true. Ultimately the user will still need to check that the phone number is correct for the customer.

Apex Interactive Report raising javascript error

I recently was working on an application in Apex 4.2.1.00.08, where the application had several pages with Interactive Reports.

On all these pages, the IR worked fine – except for one crucial page, where the IR’s action menu didn’t work (Select Columns, for example, showed a little circle instead of the expected shuttle region; all the column headings menus would freeze the page; and other issues).

In Console I could see the following errors get raised (depending on which IR widget I tried):

Uncaught SyntaxError: Unexpected token ) desktop_all.min.js?v=4.2.1.00.08:14
$u_evaldesktop_all.min.js?v=4.2.1.00.08:14
_Return widget.interactiveReport.min.js?v=4.2.1.00.08:1
b.onreadystatechange desktop_all.min.js?v=4.2.1.00.08:15
Uncaught TypeError: Object #<error> has no method 'cloneNode' desktop_all.min.js?v=4.2.1.00.08:14
dhtml_ShuttleObject desktop_all.min.js?v=4.2.1.00.08:14
_Return widget.interactiveReport.min.js?v=4.2.1.00.08:1
b.onreadystatechange desktop_all.min.js?v=4.2.1.00.08:15
Uncaught TypeError: Cannot read property 'undefined' of undefined widget.interactiveReport.min.js?v=4.2.1.00.08:1
dialog.column_check widget.interactiveReport.min.js?v=4.2.1.00.08:1
_Return widget.interactiveReport.min.js?v=4.2.1.00.08:1
b.onreadystatechange desktop_all.min.js?v=4.2.1.00.08:15

After a lot of head scratching and some investigative work from the resident javascript guru (“it looks like ajax is not getting the expected results from the server”), I found the following:

https://forums.oracle.com/message/10496937

The one thing in common was that my IR also had a Display Condition on it. In my case, the condition was based on an application item, not REQUEST. I removed the condition, and the problem went away.

I’ve tried to make a reproducible test case with a fresh application, but unfortunately with no success – which means I haven’t yet isolated the actual cause of the issue. A PL/SQL condition like “1=1″ doesn’t reproduce the problem. If I have a PL/SQL Expression like “:P1_SHOW = ‘Y’”, or a Value of Item / Column in Expression 1 = Expression 2 with a similar effect, the problem is reproduced – but only in this application.

As a workaround I’ve used a Dynamic Action to hide the IR on page load if required.

Never mind…

Update: thanks Christian for pointing out that I mistook this – it’s not CLIENT_INFO that I was using, but CLIENT_IDENTIFIER – and that behaviour hasn’t changed.

I’ve just gotten around to reading the Patch Set Notes for Apex 4.2.3, and noticed this bit:

8.5 Changes in How Oracle Application Express Populates CLIENT_INFO in V$SESSION and GV$SESSION
The Oracle Application Express 4.2.2.00.11 patch set changes how Application Express populates the CLIENT_INFO value in V$SESSION and GV$SESSION. The new information in this field is workspace ID, followed by colon (:), followed by the authenticated username.

Tip: You may have to adapt database instance monitoring scripts which interpret CLIENT_INFO and expect the previous content for Oracle Application Express sessions (username ‘:’ workspace id).

I have no idea why the patch set notes talk about “workspace id” here, since as far as I can tell, Apex actually puts the session ID there. I haven’t tested this in 4.2.3 yet though. Anyone care to verify this for me?

Performance of Apex Conditions

Just a little tip I picked up at the InSync13 conference from listening to Scott Wesley. If you have a lot of conditions that look like this:

apex-condition-plsql-expression(conditions based on a PL/SQL Expression, where the PL/SQL itself doesn’t actually call anything outside of Apex – it’s only dependent on variables that Apex already knows)

Because it’s a PL/SQL expression, the Apex engine must execute this as dynamic PL/SQL – requiring a parse/execute/fetch. This might take maybe 0.03 seconds or so. If there’s only one condition like this on a page, it won’t make any difference. But if there are 50 conditions on a page, it can make a difference to the overall page performance – adding up to 1 whole second or more to the page request, which can be noticeable.

The better alternative is to use the condition type Value of Item / Column in Expression 1 = Expression 2, e.g.:

apex-condition-item-equals-expressionThis condition type requires no dynamic PL/SQL – no parsing – which can reduce the time required to an almost negligible amount.

Image

Your ‘PL/SQL Code’

Your 'PL/SQL Code'

Am I the only one who finds this help message vaguely insulting?

dr-evil-plsql-code

Oracle VPD/RLS on Apex at InSync2013

AUSOUG is holding a series of conferences this year right across the country – starting in Sydney on 15-16 August, touring the other major city centres, and ending in Perth on 12-13 November.

The Perth program is still being finalized but the lineup is looking good. You can see the current list here: http://www.ausoug.org.au/insync13/insync13-perth-program.html

I’ll be talking about Oracle Virtual Private Database or RLS and its use in Apex applications. I’ve made good use of this technology in a recent project which is now live and I’m looking forward to presenting what I’ve learned. Abstract

Make sure you register soon – pre-registrations close soon for some locations.

UPDATE: The Perth program is now published: INSYNC13_Program_Perth.pdf

UPDATE 2: The slide deck if you’re interested can be seen here.

Link

My Function Result Cache talk

onedoesnotsimplyresultcacheIf you’re interested in my presentation on the Function Result Cache, it’s now available from my presentations page. It was given this morning at Oracle’s offices in Perth to the local AUSOUG branch and seemed to go down well and I got some good feedback. It was only a little overshadowed by all the hoopla over the release of 12c :)

Follow

Get every new post delivered to your Inbox.

Join 215 other followers