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.
Hello,
We have similar issue after upgrading to apex 5. Can you let us know which PL/SQL process we need to include.
Thanks
Hello Kartik,
It is ard ofr me to say which needs upgrading w/o seeing your data load setup. Go through your modified data upload process, and, the steps that don’t work will through an obvious error. Since I was bypassing the native APEX data upload processes, what I needed to do was make sure the collections I was maintaining included all the required columns. In the case noted I needed to add an element. I discovered this by creating a new data load wizard, and observing all collection elements at each step, comparing them to my (previous version, self-maintained) collection elements. Since tweaking the data load processes is not – technically – supported – there is no documentation for this. Hope these guidelines help! If you need some asstance, just contact me, Good luck, KC
Hello,
I see you are familiar with Data Load Wizard. I am just looking for the solution for the problem below.
There are maximum three Unique Column 1, 2 and 3 in the Data Load Definition available.
I need to create the definition for the table with the primary key consisting from 4 unique fields.
Do you have any possible solution???
Hello Frantisek,
There is a solution, but you may not like it. The APEX Data Load Wizard relies on the Primary Key definition to determine whether an incoming row is an INSERT or an UPDATE. You have two options: a) Add a synthetic key to your table, or at least your data upload table. You could for example load into a table with a synthetic key, then shift data into your main table. You would need to manage what is INSERT and what is UPDATE on that transfer into your main table. But you could still leverage all the other features of the Data Load process – transformations and validations, and table mapping. b) Use the Data Load Wizard and processes as far as the table mappings, maybe to the step after. Then you need to write your own PL/SQL procedures to habdle gettning the data in the collections into your table – bypassing the standard Data Load processes. You will of course need to handle, in your code, what is an INSERT and what is an UPDATE. I can give you more info on exactly how and where to handle those collections as need be – contact me at kcannell@thtechnology.com with more details about the transformations and validations you need to do, and I can suggest what maybe the best place to put in your own code. I am sure what you describe is possible. Good luck! KC
Hello,
thank you for your valuable comment. For me the more viable way seems to be option a), because from many uploads we do the only one table has such a primary key.
Thanks again for you advice.
Frantisek
Have a similar problem, will email you as we have not customized our data upload.
You mentioned that you customized APEX to “Allow users to upload XLSX, XLS or CSV files”. Do you have any documentation on how you did that? Thanks
Hello Troy,
I blogged about extending the APEX Data Load Wizard pages and the caveats inherent in that a few months ago, the using the EXCEL2COLLECTION APEX Plugin, I suspect from you question what you are looking for is using the EXCEL2XCOLLECTION plugin. Examine that. As I mentioned in my most recent post, if you need to upload complex (non-tabular) XLSX files, consider using Anton Scheffer’s READ_XLSX package. I suspect EXCEL2OLLECTION plugin uses READ_XLSX behind the scenes, and a few other utilities. If you have specific use cases beyond this, please let me know and I will try to help.
Pingback: Read XLXS Files from APEX 5.0 and Higher – One (or Two) Ways | TH TECHNOLOGY
APEX adds the following to the PARSE_COL_HEAD collection: C001 = USE_APPLICATION_DATE_FORMAT, C002 = E.
This weird addition is causing me a problem when I add a custom column programmatically.
If adding a column, this “E” column shows up in the table mapping page of the wizard, rather than the new column name I’m trying to add. If I map the alias I’ve created to the “E”, the file still uploads correctly, but the “E” is far from user friendly as a header.
In my case the “E” shows up in column 35, so the column I’m adding has to added as 36 (yet it still doesn’t show).
To add a custom column for the Upload Date, I added the following process to Page 1 of the wizard; it fires directly after the “Parse Uploaded Data” process. I would love some help getting rid of the “E” in the mapping screen (Page 2), so that my users don’t have to manually map that column. I’d hate to build a custom process on Page 2 to adjust the setting, but that’s all I can think of.
Declare
v_date date;
BEGIN
SELECT SYSDATE INTO v_date FROM Dual;
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => ‘PARSE_COL_HEAD’,
p_c001 => ‘C036’,
p_c002 => ‘UPLOAD_DTE’);
FOR UPLOAD_ROW IN (SELECT SEQ_ID FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = ‘SPREADSHEET_CONTENT’)
LOOP
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
p_collection_name => ‘SPREADSHEET_CONTENT’,
p_seq => UPLOAD_ROW.SEQ_ID,
p_attr_number => ’36’,
p_attr_value => v_date);
END LOOP;
END;
I’ve solved this issue I noted above. See code below:
Declare
v_date date;
BEGIN
SELECT SYSDATE INTO v_date FROM Dual;
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => ‘PARSE_COL_HEAD’,
p_c001 => ‘C036’,
p_c002 => ‘UPLOAD_DTE’);
APEX_COLLECTION.MOVE_MEMBER_DOWN(
p_collection_name =>’PARSE_COL_HEAD’,
p_seq => ’36’);
FOR UPLOAD_ROW IN (SELECT SEQ_ID FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = ‘SPREADSHEET_CONTENT’)
LOOP
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
p_collection_name => ‘SPREADSHEET_CONTENT’,
p_seq => UPLOAD_ROW.SEQ_ID,
p_attr_number => ’35’,
p_attr_value => v_date);
END LOOP;
END;
Tony,
You encountered exactly the issue I noted in my post – the extra ‘E’ comes from the USE_APPLICATION_DATE_FORMAT option.
You forced a fix for your specific situation.
It works because it appears that you have a fixed number of columns, and because you are forcing the data you want in there, for your purpose.
All is good for you – this may not work for other use cases.
Just watch that this continues to work as you upgrade, as the post warns.
Good luck!
Hi,
Maybe you also encountered below error when action is Update
Error: ORA-01747: invalid user.table.column, table.column, or column specification record number 1 error_statement : update.
Thank you!
No, I didn’t. All I can think of is to check the column names … are any of these reserved words that might mess up a query? Just a guess. When do you get this error? Wish I could help more …
Hello,
for uploading xlsx files I employ Anton’s as_read_xlsx package successfully. To upload xlsx files into the standard DLW I added to my procedure DATA_TO_COLL a functionality for populating the PARSE_COL_HEAD and SPREADSHEET_CONTENT collections.
It works fine when user goes Next to the Data / Table Mapping page. The data in the mapping table looks fine, also when I explore session state I see correct data in both collections.
But the error No data found arises when user goes Next to the Data Validation page, while the standard Parse Uploaded Data process preceeds my DATA_TO_COLL procedure. If my procedure runs first followed by standard Parse Uploaded Data process, any error arises, but the content of both collections is rewrited by unvalid characters.
Could you advise me how to fix it? Where shall be the right execution point of my custom procedure? Shall I delete the call of standard Parse Uploaded Data process or not?
Thanks in advance.
Frantisek
Pingback: APEX Data Load Wizard Customizations ~ Revisited - TH TECHNOLOGYTH TECHNOLOGY