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.

Data-Loading-Failed
(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.