Skip to content

Too Much Validation is Too Much

I had built and was managing a web site that takes registrations from thousands of people around the state for a variety of sporting events. One of the goals of the site is to collect better quality data for the people running the events, i.e. they basically needed to get a better handle on how many people were actually attending.

One of the other goals of the site was to make it as easy and hassle-free as possible for anyone to register. This meant that requiring people to sign up for an account with usernames and passwords was undesirable, so make it possible for someone to just sign up with all their info in one session (i.e. never authenticated), then never return.

This also meant that if someone started to register one day, but abandoned their “shopping cart” (so to speak), and then came back the next day, they happily re-entered all their info again – which caused duplicate records to appear in the database. Someone accidentally closes their browser – another duplicate record. Someone signs up their friend on their behalf, not knowing their friend had already signed up – another duplicate record. Someone with very little computer experience gets an error (e.g. “Date of birth must be entered.”) and responds by closing the browser and restarting – and doing this multiple times – we got five duplicate records from this person.

So I built an automated alert system which would email the team coordinators a list of the duplicate records, based on a simple case-insensitive match on first name + surname (we did have one case last year where two different people happened to have the same name, but this is a very rare occurrence when you’re talking about only a few thousand people). I also built a de-duplicator which allowed me to compare two records side-by-side and delete one of them.

In the crunch week (the week before nominations close), we were getting 40+ registrations per day – and each day I was deleting 4 or 5 duplicate records. I thought there must be a better way.

So I (naïvely) quietly added a simple validation check to the signup page – if the player’s name was already registered it showed the error message “Sorry, a registration under you name has already been created. Please login to change your registration.” along with my contact details.

usererrorIt worked, kind of – immediately I got 3 emails and 1 phone call from people who had started their signup, having earlier ignored or missed the email with their login details, and tried to sign up again. I made sure they could login, and they were able to update their existing registration without creating duplicate records. I was quietly optimistic that it would work better now.

Unfortunately, I was wrong. A few days later (today, actually) I decided to do an audit to see if my change had actually made things better or not. I suspected that some people might ignore that error message and just put in a different name. My suspicion was warranted, as it turns out.

So far I’ve gone through over 800 records and found variances of “Bloggs, Joe” or “Bloggs, Joe B” or “Bloggso, Joe” or “Bloggs Is My Name, Joe” scattered throughout. All my validation had done was put a roadblock in front of the users, who simply drove around it by putting in a slightly different name (I saw a lot of them simply put in their middle name), and now (more importantly) my de-duplicator is useless because it only finds matches on exact given names and surnames.

I’ve removed the validation. The duplicate records are manageable, and the system is overall easier for everyone with less validation.

RETURNING RECORD INTO

This is an idea for an enhancement to the PL/SQL syntax.

If I have the following declaration:

DECLARE
  in_record mytable%ROWTYPE;
  out_record mytable%ROWTYPE;
BEGIN

I can do this:

  INSERT INTO mytable VALUES in_record;

I can also do this:

  UPDATE mytable SET ROW = in_record WHERE ...;

I can do this, as long as I list each and every column, in the right order:

  INSERT INTO mytable VALUES in_record
  RETURNING cola, colb, colc INTO out_record;

But I can’t do this:

  INSERT INTO mytable VALUES in_record
  RETURNING ROW INTO out_record;

Can we make this happen, Oracle?

Grassroots on Apex 5

Scott Wesley has started an excellent series of posts exploring the Early Adopter release of Apex 5.0.apex5

I’m excited about the changes in 5.0 but have been too busy both at work and at home to look into it personally – so for now reading other people’s reviews has had to suffice. I’m looking forward to hopefully hearing more about it at AUSOUG later this year.

My advice is, have a look and give it a go sometime. In other words, do as I say, not as I do :)

Don’t mess with my page, bro

One of my clients reported an issue – they were seeing “Waiting for 1.2.3.4″ and a blank screen when they tried to access the Apex web site I’d built for them. They were using Mozilla on a Windows PC, connecting via Vodaphone 3G – the problem was consistent, and it went away when they used their ADSL connection.

My initial response was “don’t use Vodaphone 3G” because the problem seemed to be outside of my area.  It appears to be a common issue, something that some mobile operators do to reduce image sizes – c.f. https://support.mozilla.org/en-US/questions/791180  and http://www.geekstogo.com/forum/topic/277895-suspected-issue-waiting-for-1234-in-firefox-on-at/

My client did a little more digging (he’s a techie as well) and found this: http://stackoverflow.com/questions/4113268/how-to-stop-javascript-injection-from-vodafone-proxy

After reading that I said I’d give it another go and see what could be done. As far as I could see, the only really viable solution is to add the “Cache-Control: no-transform” header to the responses. Since I’m using Apache, to do this I added the following to my apache config as per http://httpd.apache.org/docs/current/mod/mod_headers.html:

Header merge Cache-Control no-transform

That seemed to fix the problem. What this header does is instruct all intermediaries to not modify the content in any way – i.e. don’t try to recompress the images, don’t inject any extra CSS or javascript into the page, nothing. Adding this header does carry the risk that performance on some mobile networks may suffer (because they will no longer do the image compression), so it’s now up to me to make sure my pages and images are as small as possible.

Don’t (always) call v()

Instead of calling a function, when you can get the same effect by accessing a documented PL/SQL variable, you should. For example:

v('APP_USER')    = APEX_APPLICATION.g_user
v('REQUEST')     = APEX_APPLICATION.g_request
v('APP_ID')      = APEX_APPLICATION.g_flow_id
v('APP_PAGE_ID') = APEX_APPLICATION.g_flow_step_id
v('DEBUG')       = APEX_APPLICATION.g_debug

(Note – g_debug is a boolean, unlike the v() equivalent)

There’s more here: documentation for the APEX_APPLICATION package

I suspect that the implementation of v() is something like this [EDIT: read the comments for more commentary on this, and a more accurate picture of what v() actually does]:

FUNCTION v (p_name IN VARCHAR2) RETURN VARCHAR2 IS
  res VARCHAR2(4000);
BEGIN
  CASE p_name
  WHEN 'APP_ID' THEN
    res := APEX_APPLICATION.g_flow_id;
  WHEN 'APP_USER' THEN
    res := APEX_APPLICATION.g_user;
  WHEN 'DEBUG' THEN
    IF APEX_APPLICATION.g_debug THEN
      res := 'YES';
    ELSE
      res := 'NO';
    END IF;
  WHEN 'REQUEST' THEN
    res := APEX_APPLICATION.g_request;
  ... etc. ...
  ELSE
    BEGIN
      SELECT s.item_value
      INTO res
      FROM wwv_<session-values-or-something> s
      WHERE s.item_name = p_name
      AND s.flow_id = APEX_APPLICATION.g_flow_id
      AND s.session_id = APEX_APPLICATION.g_instance;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    END;
  END CASE;
  RETURN res;
END v;

In addition, instead of calling v('APP_SESSION') / v('SESSION'), you could call the undocumented function APEX_APPLICATION.get_session_id instead, which is probably faster, or refer to the global variable APEX_APPLICATION.g_instance instead. I would suspect that the function normally just returns g_instance anyway, but it’s possible there’s some more logic behind the function.

Disclaimer: use undocumented bits at your own risk.

Some other undocumented goodies that may be useful include (and a lot of these are not available at all via v()):

APEX_APPLICATION.g_flow_alias = application alias
APEX_APPLICATION.g_flow_name = application name
APEX_APPLICATION.g_flow_version = application version string
APEX_APPLICATION.g_flow_status = app availability status code, e.g. AVAILABLE_W_EDIT_LINK
APEX_APPLICATION.g_build_status = app build status code, e.g. RUN_AND_BUILD
APEX_APPLICATION.g_base_href = the base URL for the site, not including the f?p=... bit
APEX_APPLICATION.g_printer_friendly = TRUE if the page was requested with Printer Friendly flag
APEX_APPLICATION.g_excel_format = TRUE if the page’s report is being rendered in CSV format
APEX_APPLICATION.g_date_format = Application default date format
APEX_APPLICATION.g_date_time_format = Application date time format
APEX_APPLICATION.g_timestamp_format = Application default timestamp format
APEX_APPLICATION.g_timestamp_tz_format = Application default timestamp with time zone format

You can have a peek at all the globals in this package with this query (but be warned, any undocumented ones may change, and may not necessarily be set to any meaningful value when your code is running):

select owner, trim(text)
from dba_source
where name = 'WWV_FLOW'
and type = 'PACKAGE'
and ltrim(text) like 'g%'
order by owner desc, line;

Proposed wrapper for APEX_UTIL.set_session_state

I decided to try using a wrapper procedure to isolate calls to APEX_UTIL.set_session_state in an autonomous transaction. I’m currently using it in a project and seeing how it goes in terms of performance.

DISCLAIMER: Don’t just throw this into your mission-critical system without at least testing it thoroughly first.

Since I had Morten Braten’s Alexandria library handy, I simply modified his APEX_UTIL_PKG. If you’re not using this library you can create your own wrapper quite simply:

create or replace procedure sv
  (p_name  in varchar2
  ,p_value in varchar2 := NULL) as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  APEX_UTIL.set_session_state
    (p_name => p_name
    ,p_value => p_value);
  COMMIT;
end sv;

Since my system has many schemas (one for each application), I would compile this in a “common” schema and then grant execute on it to the schemas that need it, and create local synonyms in each one so that my applications just need to call sv.

ADDENDUM:

As Joel Kallman rightly points out, putting set_session_state in an autonomous transaction means that the new value will not be visible to the rest of the calling code, so for example the call to v() will not return ‘Joe’ here:

sv('P1_NAME', 'Joe');
x := v('P1_NAME'); -- will not be 'Joe'

Therefore, it is intended that sv() be used as the final step in any procedure, e.g.:

PROCEDURE p1_controller IS
  p1_name VARCHAR2(100);
BEGIN
  p1_name := v('P1_NAME');
  <business logic that does something with/to p1_name>
  sv('P1_NAME', p1_name);
END;

APEX_UTIL.set_session_state may or may not commit

When should you commit or rollback a transaction? As late as possible, I would have thought, based on most of the advice in the Oracle world. You certainly want this to be predictable and consistent, at least.

Unfortunately, if you use APEX_UTIL.set_session_state in your PL/SQL process, the result is not so predictable.

Thanks to Martin D’Souza who alerted me to this. I love learning new things, but occasionally you get a bad surprise like this and it’s not so pleasant.

Test case set up – create a table with a single row, and create a simple Apex application with one page, with one region, with an item (P1_N) and a Submit button.

CREATE TABLE test (N NUMBER);
INSERT INTO test VALUES (1);
COMMIT;

TEST CASE #1

Add an On Submit process to the page which fires when the Submit button is clicked, which executes the following:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 1);
  ROLLBACK;
END;

What value would you expect to see in the database table now? I would have expected that the table would hold the value 3 – and indeed, it does.

TEST CASE #2

Modify the process slightly – after the second update, set the item to something different:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 4); --changed here
  ROLLBACK;
END;

This time, the second update to the table has been committed before we issued our ROLLBACK. The new value 2 has been saved to the database. Why?

It’s because APEX_UTIL.set_session_state will issue a COMMIT – but only if the value of the item is changed. If you happen to call set_session_state with the value that the item already has, it does nothing, and does not COMMIT. I understand why a COMMIT is ultimately necessary (Apex session state is stored in a table) – but I disagree that it’s necessary for it to commit my (potentially partial) transaction along with it.

This means that if an exception is raised somewhere in my process, the resulting rollback may or may not rollback the entire transaction, depending on whether any prior calls to set_session_state happened to COMMIT or not. This is difficult to predict and therefore makes debugging harder. Not to mention the fact that it violates the general principle of “either the whole transaction succeeds and is COMMITted, or it fails and the whole transaction is rolled back”. I’m sorry, Apex, but you should not arbitrarily commit part of my transaction without at least telling me.

Mitigations for this? I’m not sure yet. One suggestion from this forum thread was to make the procedure use an autonomous transaction. This would align it more closely to what most developers would expect, I think. Unfortunately it appears the suggestion was rejected (or put on hold indefinitely).

I’m planning on refactoring my code to shift all calls to set_session_state to as late in the process as possible; in addition, I’m thinking that I would put an explicit COMMIT prior to these calls so that my code would have more predictable behaviour. But the idea of wrapping set_session_state in a wrapper procedure with an autonomous transaction seems good to try out as well.

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:

Follow

Get every new post delivered to your Inbox.

Join 180 other followers