APEX 19.2: Bug/Undesirable feature: Untrappable error from data parser

fac586

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.)

Carsten Czarski-Oracle

Hi,

APEX_DATA_PARSER determines the file type simply from the file extension. To avoid running into this situation I would recommend to simply check the file using APEX_DATA_PARSER.GET_FILE_TYPE first. This returns a constant of the APEX_DATA_PARSER.T_FILE_TYPE type for known file types, and NULL otherwise.

The PARSE function also accepts the p_file_type parameter which can be used instead of p_file_name. That allows you to become independent from extensions.

if APEX_DATA_PARSER.GET_FILE_TYPE( p_file_name => p_filename ) is not null then

-- your code here

end if;

Regarding the error message you're right. A file type error is raised as an "Internal Error"; which you cannot simply catch in your PL/SQL exception handler. The DISCOVER function actually raises an internal error when the file type (see above) is null. That is something we can have a look at.

For now, I would recommend to just add the additional IF clause using GET_FILE_TYPE to your code.

Does that help

Best regards

-Carsten

Carsten Czarski-Oracle

Hi,

APEX_DATA_PARSER determines the file type simply from the file extension. To avoid running into this situation I would recommend to simply check the file using APEX_DATA_PARSER.GET_FILE_TYPE first. This returns a constant of the APEX_DATA_PARSER.T_FILE_TYPE type for known file types, and NULL otherwise.

The PARSE function also accepts the p_file_type parameter which can be used instead of p_file_name. That allows you to become independent from extensions.

if APEX_DATA_PARSER.GET_FILE_TYPE( p_file_name => p_filename ) is not null then

-- your code here

end if;

Regarding the error message you're right. A file type error is raised as an "Internal Error"; which you cannot simply catch in your PL/SQL exception handler. The DISCOVER function actually raises an internal error when the file type (see above) is null. That is something we can have a look at.

For now, I would recommend to just add the additional IF clause using GET_FILE_TYPE to your code.

Does that help

Best regards

-Carsten

fac586

Hi Carsten

Many thanks for responding so rapidly with an effective solution.

We were using if logic to determine whether to call the discover function, but that was checking the MIME type from APEX_APPLICATION_TEMP_FILES, which for some reason turned out not to be consistent across development and test environments. Using the same method as the parser itself clearly makes sense.

I think that raising an internal error for unsupported file types in discovery or parsing is something that should be reconsidered. I am not aware of the "internal error" concept being documented or described anywhere, but from the comments in the sample error handling function, passing an unsupported file type to the parser is not an issue of the same severity as "an invalid statement or code that can't be executed". Throwing a standard exception in this case would offer PL/SQL developers more flexibility when using apex_data_parser, particularly when bulk processing of multiple files. If raising an internal error is necessary when the package is utilised by the APEX engine or IDE then it would seem to be easy to do so as a result of the exception.