APEX CSV Import: “Data Loading Failed”
If you are using the APEX built-in Data Loading feature to allow your users to upload CSV files, you may have encountered this error.
(Note: the error may appear differently in your application as I have built a custom error handling function)
It’s not a particularly useful message, and the logs don’t seem to shed much light on the problem either – reporting only the following:
DATA_LOAD - Final collection is created ...Execute Statement: select 1 from "DEMO"."MY_TABLE" where "RECORD_ID" = :uk_1 Add error onto error stack ...Error data: ......message: Data Loading Failed ......additional_info: ORA-01403: no data found ... ......ora_sqlerrm: ORA-01403: no data found ......error_backtrace: ORA-06512: at "APEX_040200.WWV_FLOW_DATA_UPLOAD", line 4115 ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS_NATIVE", line 213 ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS_NATIVE", line 262 ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN", line 1808 ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS", line 453
After trial and error I tracked down one potential cause of this error so I thought I’d share it in case it happens again. I’ll probably come across this again later and forget what the solution was and find this article.
In my case (APEX 4.2.4), the problem was caused by an invalid entry in the Column Name Aliases list of values. I was using a custom List of Values so that alternative names for the columns would be automatically mapped without the user having to select them every time. To do this, I had to edit the List of Values directly to add the alternative names; but I had mistyped one of the Return Values which must map to a real column name on the target table. Whenever I picked this column for an import, I’d get the “Data Loading Failed” error message. Correcting the return value resolved the issue.
In order to stop this happening again, I added the following check to my Apex QA script (this is run whenever the application is deployed):
PROMPT Invalid dataload column mappings (expected: none) SELECT REPLACE(lt.owner,'#OWNER#',USER) AS owner ,lt.table_name ,le.return_value AS target_column_not_found ,le.list_of_values_name ,le.display_value AS col_alias ,lt.application_id ,lt.application_name ,lt.name AS dataload_definition FROM apex_appl_load_tables lt JOIN apex_application_lov_entries le ON le.lov_id = lt.column_names_lov_id WHERE NOT EXISTS ( SELECT NULL FROM all_tab_columns tc WHERE tc.owner = REPLACE(lt.owner,'#OWNER#',USER) AND tc.table_name = lt.table_name AND tc.column_name = le.return_value) ORDER BY lt.name, le.display_sequence;
If the above query returns any rows, it’ll be a problem.