Skip to Main Content
Feature Request FR-4079
Product Area Application Builder
Status CLOSED

2 Voters

Provide detailed error messages for data loader - including the reason and the row and field of data that is causing the issue.

stirling.butcher Public · Oct 25 2024

Idea Summary
Using the built in data loader is great - until something goes wrong and it isn't.  When data fails to load it can be hard for me as a developer to find the reason and the offending data - it's almost impossible for a end user.
Please provide detailed error messages to users of the built in data loader - including the reason,  where possible the row (PK) and the field/column that is causing the issue.  Where possible display the offending data value on screen with the reason

Use Case
This would be useful for any app that loads data.

Preferred Solution (Optional)
I don't have a preferred solution - but a team I used to work in achieved something like this by loading into a giant table with large varchar2 columns and then row by row inserting into the desired table which allowed us to catch the row that failed and the reason the row failed - which usually identified the column so we could give users really helpful error messages.  I get it its slower - maybe give an option for detailed error messages or faster loading.  When a couple hundred rows is all thats going to be loaded  - which I am sure is often the case - extreme efficiency of speed isn't a priority

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Comments

Comments

  • carsten.czarski APEX Team OP 8 months ago

    Did you try out to set an Error Handler?

    On the Edit Data Load page, look up Error Handling, and switch the When on Error select list to Log Error into Collection. The collection will then contain exactly what you need.

  • stirling.butcher OP 8 months ago

    @carsten.czarski I don't think it does - it might give a error message but does it give the data that caused the error?  Or does the collection even the name of the column involved? It's certainly not something that can be understood by a user and allow them to fix the data they are trying to load.

    I've seen this cause issues in multiple organisations

  • carsten.czarski APEX Team OP 8 months ago

    You'll get the row number from the uploaded file in the N001 collection member, and the error message for that row in the C001 collection member. It's up to the developer to present that to the end user in a meaningful way.

    Regarding the offending column, I'm afraid that APEX cannot provide information which it does not have. When you perform an INSERT or UPDATE operation on a table row, and one of the columns has a bad number format, the database only responds with e.g. ORA-01722: invalid number - but the database does not return the actual column. 

    So, if APEX does not get that information, APEX cannot return it - we can only return the row number in the CSV file and the concrete error message. Using the row number, you can easily extend your application to grab the offending row from the uploaded file - and to visualize that to end users in a meaningful way.

  • stirling.butcher OP 8 months ago

    Thats interesting.  A team I previosly worked for made a custom loader that did return the column and offending data.  Possibly they checked constrainte and data definition against the data!?

    However I didn't realise we do have the rownumber in the source file - that does help.  Thanks for explaining that!

  • carsten.czarski APEX Team OP 8 months ago

    Possibly they checked constrainte and data definition against the data!?

    One might be able to extract the constraint name from the error message and then do some dictionary lookup to get column names. However, that would be an incomplete solution, as it would only catch errors for which we have a constraint (i.e. it would not catch the ORA-1722 error shown above). And the constraint must be a column constraint - row level check constraints would not help. 

    All in all I don't see looking into the constraints as a feature of the APEX engine - this is the domain of custom coding … and if such requirements exist: One could use the built-in data loading to load into a collection or staging table, and then apply the custom logic to load into the target table - with whatever custom logic is needed or wanted.