I was revisiting our APEX Data Load application, which uses a highly customized version of the APEX Data Load Wizard – several of them. Key words here: Highly Customized.

Customizing the Data Load Wizard – or modifying any set feature of APEX – leaves one open to the possibility that things might not work in the following versions. This has been the case with every major APEX upgrade since we made the Data Load Wizard customization in APEX 4.2.  Each time we have had to plan time/resources to revisit the customizations and make corrections – all on our own sleuthing.  No big deal, right?

This year an upgrade to APEX 5.1,  left the last step, the load into our defined Data Load table, not working. I had two choices, rebuild the Data Load Wizard pages ( 3 sets), and re-apply the customizations.  Or, write my own load into the final table.

This time I chose the latter.
As a quick reminder, the Data Load Wizard loads uploaded data into a series of collections.  To learn more about the Data Load Wizard and why we customized, read the previous Data Load Wizard posts. The data from those collections – after transformations are applied – gets loaded into the Data Load target table.  In our case, we needed complex validations and transformations, and logging of each change. The logging of each change is what really drove us to customizing the process. So we pulled data from the uploaded collection (SPREADSHEET CONTENT) into a temporary table, performed all our validations, transformations and logging, then moved the data back into the load collection (LOAD_CONTENT).  And continued with the Data Load process.  That’s a simplification, there are a lot of other pieces involved, but that’s enough for here. (Read previous blog posts for more details).

By choosing to write my own load into the final table, and bypass the APEX processes, I lose some other features that I now have to code on my own.  What I lose:

• Automatic insert/updates
• Automatics insert/update failure counts and logging.

I am OK with handling these in my own bulk process insert with the LOG ERRORS clause. We only get inserts, never updates, which simplifies things.  Your mileage may vary. Note that if you need to process updates and inserts, and report which records are inserted vs updated, the solution will be more complicated.

Next time we upgrade, I expect another set of minor Data Load Wizard changes … to which I will adjust somehow.  Now, IF the Data Load Wizard were to allow greater flexibility in logging transformations for each row – then I may be able to abandon the customized code altogether and revert to the mainstream Data Load Wizard.  Until then I am resigned to maintenance with each update.

P.S.
I just had someone ask about customizing the Data Load Wizard – breaking into the collections as I did. My recommendation was, use the Data Load Wizard to load into a staging table, then write PL/SQL processes to perform the validations needed – in his case to only do inserts no updates – filter out existing rows. Such a solution will be in-the-sandbox (no customizations) and will readily upgrade.
I strongly advise against breaking into the Data Load Wizard unless there is a solid business requirement to do so. Unless …

• • You have plenty of time to go back and fix things upon upgrade, and
  • You have a finding Fairy to pay for all those upgrades.

Happy Coding, whichever approach you choose.