APEX 19.2.0.00.18
ORDS 19.4.0.r3521226
Oracle Database 19.5.0.0.0 Enterprise Edition
We have an application that contains data upload functionality. This consists of several custom wizard pages and processes using apex_data_parser API methods. CSV is the only permitted file format, and support for multi-file uploads is a must-have requirement. To accommodate this, we followed the guidance in the RFC 4180 CSV documentation to "be conservative in what you do, be liberal in what you accept from others". As designed, we accept whatever files the user uploads, determine whether they are valid CSV files per RFC 4180 and our required data format, store this information in a collection, and display the status of each file to the user in a report on a page in the wizard. Invalid files will be ignored in subsequent processing stages of the wizard.
Developing this was fairly straightforward. Everything works as intended when CSV files are uploaded. It continues to do so if we sneak in some random non-CSV files with .csv extensions. And with XLSX, XML, and JSON files that are supported by apex_data_parser. These all effortlessly progress to page 2 of the wizard where the interlopers are listed in the exception report. Introduce an unsupported file type into the mix though and something unexpected happens. Page submission is blocked and an error message stating "This file type is not supported by the parser" is displayed:
This is baffling. There are no validations that show this error on the page. We are explicitly catching this exception in a local procedure in the code called by the page process, and not propagating it any further:
/*
* Parse the file using the apex_data_parser package to determine the file
* type and extract a file profile.
*
* @param p_blob_contents The data contents of the file.
* @param p_filename The filename of the file, including its extension.
*/
procedure parse_file (
p_blob_contents in out nocopy blob
, p_filename in varchar2)
is
begin
l_file_profile_json := apex_data_parser.discover(p_blob_content, p_filename, p_max_rows => k_max_rows);
l_file_profile := apex_data_parser.json_to_profile(l_file_profile_json);
log_pkg.log_info(apex_string.format('...file profiled: file_type = %s; rows = %s', l_file_profile.file_type, l_file_profile.parsed_rows));
exception
when unsupported_file_type
then
l_file_profile := null;
l_file_columns := apex_t_parser_columns();
log_pkg.log_warn(log_pkg.sprintf('Encountered unexpected file type while parsing ad hoc adjustment file "%s".', p_filename));
end parse_file;
We can see the log entries that prove that the exception handler is being executed:
ID LOGGER_LEVEL TEXT TIME_STAMP SCOPE
1954531 4 Encountered unexpected file type while parsing data upload file "ndfp-420-png.png". 08.04.2021 14:46:58.819275000 data_upload.stage_file.parse_file
There are no other invocations of apex_data_parser API methods during accept processing on the page.
So why is submission being blocked? Where is the error message coming from?
Eventually I noticed something in the debug log:
...
0.07746 0.00002 Processes - point: AFTER_SUBMIT 4 0%
0.07748 0.00001 > APEX_APPLICATION_PAGE_PROCESS Stage & validate uploaded files 3 0%
0.07749 0.00032 "...Process ""Stage & validate uploaded files"" - Type: NATIVE_PLSQL" 4 0%
0.07781 0.00176 begin begin 4 1%
-- Stage and validate the uploaded files in working storage. --
data_upload.stage_files(apex_string.split(:p320_upload_csv_files, ':'));
data_upload.validate_files();
end;
end;
0.07957 0.00019 Collection - Getting collection ID for Collection Name: DATA_FILES 4 0%
0.07976 0.00002 Collection - Returning Collection ID 16779990035786 for Collection Name: DATA_FILES 4 0%
0.07978 0.00008 Collection - truncate_collection_int: 16779990035786 4 0%
0.07986 0.00002 Collection - End truncate_collection_int: 16779990035786 4 0%
0.07988 0.00006 Collection - Getting collection ID for Collection Name: DATA_FILE_COLUMNS 4 0%
0.07993 0.00001 Collection - Returning Collection ID 16824313035787 for Collection Name: DATA_FILE_COLUMNS 4 0%
0.07995 0.00004 Collection - truncate_collection_int: 16824313035787 4 0%
0.07998 0.00511 Collection - End truncate_collection_int: 16824313035787 4 2%
0.08509 0.00374 Add error onto error stack 4 2%
0.08883 0.00008 Error: This file type is not supported by the parser. 1 0%
0.08891 0.00002 - additional_info: Contact your application administrator. 4 0%
"Details about this incident are available via debug id ""176303""."
0.08893 0.00003 - display_location: ON_ERROR_PAGE 4 0%
0.08896 0.00001 - is_internal_error: true 4 0%
0.08897 0.00001 - is_common_runtime_error: false 4 0%
0.08898 0.00003 - apex_error_code: WWV_FLOW_FILE_PARSER.UNKNOWN_FILE_TYPE 4 0%
0.08901 0.00002 - error_backtrace: ----- PL/SQL Call Stack ----- 4 0%
object line object
handle number name
913c03d0 1033 package body APEX_190200.WWV_FLOW_ERROR.INTERNAL_GET_ERROR
913c03d0 1101 package body APEX_190200.WWV_FLOW_ERROR.INTERNAL_ADD_ERROR
913c03d0 1493 package body APEX_190200.WWV_FLOW_ERROR.RAISE_INTERNAL_ERROR
89c1c2c0 5404 package body APEX_190200.WWV_FLOW_DATA_PARSER.DISCOVER
89d6efc8 124 package body APP.DATA_UPLOAD.STAGE_FILE.PARSE_FILE
89d6efc8 250 package body APP.DATA_UPLOAD.STAGE_FILE
89d6efc8 341 package body APP.DATA_UPLOAD.STAGE_FILES
89c5cf68 7 anonymous block
975343e8 2120 package body SYS.DBMS_SYS_SQL.EXECUTE
9a432e40 601 package body SYS.WWV_DBMS_SQL_APEX_190200.EXECUTE
97908158 2550 package body APEX_190200.WWV_FLOW_DYNAMIC_EXEC.RUN_BLOCK5
97908158 1499 package body APEX_190200.WWV_FLOW_DYNAMIC_EXEC.EXECUTE_PLSQL_CODE
8eb85068 2656 package body APEX_190200.WW~
0.08902 0.00002 - component: APEX_APPLICATION_PAGE_PROCESS Stage & validate uploaded files (11032045284399916) 4 0%
0.08904 0.00009 ...Show Error on Error Page 4 0%
0.08913 0.00287 ......Performing rollback 4
...
In the call stack entry at 0.08901 there is a reference to APEX_190200.WWV_FLOW_ERROR.INTERNAL_ADD_ERROR. This leads me to think that an error is being pushed on to the APEX error stack before the corresponding PL/SQL exception is thrown. The result is that while we are able to trap the exception at the PL/SQL level, by that point the APEX engine has been notified that a fatal error has occurred in a page process and that page submission has therefore failed. APEX then re-renders the current page to display the error instead of branching to the next page in the wizard.
Can someone (@carsten-czarski-oracle?) with knowledge of the workings of the data parser and/or internal APEX error handling confirm this theory?
Is this a bug or intentional behaviour with unintended consequences in this case? Uncontrollably propagating a submission-blocking error arising from processing a single file isn't really compatible with support for bulk upload and processing of multiple files...
Is it avoidable by some means that we have not identified? (And yes, we have thought about working round it by only trying to parse supported file types, but MIME types seem to have problems of their own, and users can put a .csv extension on any file they like. The parser is doing exactly what we need—except for creating this error condition that we are apparently unable to trap and suppress.)