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.