APEX Data Load Wizard Customizations ~ Revisited

dataLoadWizard
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.

APEX 5 Data Load Wizard ~ Slight Updates Mean Changes to Customized Load Pages

Slight changes in the APEX 5 Data Load Wizard meant our customized Data Load pages needed some attention to work properly in APEX 5.

Ever customize the APEX Data Load Wizard pages?  We did so in APEX 4.2,  to

  • Accomodate data change logging requirements. We needed to log every change made to incoming data.  The APEX Data Load Wizard will make those changes for you, but not log them.
  • Allow users to upload XLSX, XLS or CSV files.

So we customized. And all worked well, until we upgraded to APEX 5.

In APEX 5, all our data uploads all broke, giving an abrupt “no data found” error during the file upload.  Geez, it hadn’t even got to all our custom stuff yet!

Well, a little digging into the APEX 5 Data Load Wizard-generated pages and collections gave me the answer. And we are back in business.

This is good reminder that any time you play outside the APEX sandbox – use anything other than the standard wizards and documented APIs –  you are subject to things breaking upon upgrade. We know this and accept it … and we really should PLAN for it. Even point releases.

So what changed?

The Data Load Wizard uses a series of collections behind the scenes to store the data being uploaded and processed:

  • SPREADSHEET_CONTENT – Uploaded (or copied) data
  • PARSE_COL_HEAD – As-uploaded column headings
  • LOAD_CONTENT – Content to be loaded, plus Error and Action information in c047, c048 and c049.
  • NOT_LOAD_CONTENT – Content to NOT load, including the same Error and Action information in c047, c048 and c049.
  • LOAD_COL_HEAD – Mapped column headings; the columns to be loaded.

The collection names pretty much tell you what each collection does.

The APEX 5 DLW now also creates a new collection:

  • SPREADSHEET_SMTP – One member, the uploaded spreadsheet sheet name.  (Not sure what happens if there are multiple sheets … task for another day.)

Was this the problem?  No.  This new collection was getting created in APEX 5 – clearly not the problem.

What was? Behind the scenes, the APEX 5 DLW added a new element as  the last element in the PARSE_COL_HEAD collection, USE_APPLICATION_DATE_FORMAT.  As soon as I added this element to my collections, all worked happy again.

How did I find it?  Plain old triage. I knew from experience that upon upload the SPREADSHEET_CONTENT and PARSE_COL_HEAD columns got populated. Since we did not get beyond the upload- clearly something was happening  – missing – there.  I created some APEX 5 data load wizard pages based on the same table, and step by step examined what was there or not in session state.  Our customization populated PARSE_COL_HEAD (for a variety of reasons re saving the uploaded file, off point for this post).  But it was not adding the USE_APPLICATION_DATE_FORMAT element.  A simple PL/SQL change corrected that, and the no-data-found went away.

Note that this solved our APEX 5 data load issue.  I have seen other cases of the same no-data-found message on other posts in the APEX Community and stackoverflow.com – these may or may not be the same issue I describe here, and the pre-APEX 5 posts are definitely not. Suffice it to say if one customizes any part of the APEX Data Load Wizard, one adopts  the responsibility of keeping up with all of the nuances of the Data Load Wizard for the life of that application.

Is there something else different behind the scenes?  Yes, a few things off topic for this post.  Is there more? Could be, but it is not effecting my customized data load  pages right now.  Will it break on the next upgrade?  Maybe, and we will of course be watching, testing and prepared to adjust as needed.

Lessons reinforced:  Customize only when necessary.  Plan for, allocate time for, test, and adjust as necessary before every upgrade. Common sense stuff that sometimes gets back-seated in the haste to upgrade.