Hello,
We use APEX_DATA_PARSER to parse and insert data from Excel files into our database. With Apex 19.1 everything works alright, but when testing with 20.2 we get exceptions while parsing some Excel files.
For example this file: https://stateaid.minfin.bg/document/846 (original page is https://stateaid.minfin.bg/bg/page/483)
SELECT *
FROM (TABLE(APEX_DATA_PARSER.PARSE(P_CONTENT => UTILITY.READ_FILE_BLOB('UPLOAD', 'deggendorf.xlsx'),
P_XLSX_SHEET_NAME => 'sheet1.xml',
-- P_SKIP_ROWS => 5,
P_FILE_NAME => 'dummy.xlsx')))
Note: UTILITY.READ_FILE_BLOB is our package made to load files from filesystem into blob variable.
Exception we get is:
ORA-06502: PL/SQL: numeric or value error: character string buffer is too small
We narrowed down the issue to the internal discovery of the document fields and their lenghts and types. To workaround the issue we constructed slimmed down P_FILE_PROFILE json document which if supplied shortcuts the parsing and sql returns all data we need.
{
"file-type":1
,"file-encoding":"AL32UTF8"
,"headings-in-first-row":true
,"xslx-worksheet":"sheet1.xml"
,"csv-enclosed":"\""
,"force-trim-whitespace":true
,"columns":[
{
"name":"COL001"
,"data-type":1
,"data-type-len":4000
}
]
,"parsed-rows":117
}
I guess when parser tries to construct the json internally, it takes the data from the excel cells and tries to use it as name of the parsed field, thus exceeding the lenght of the internal NAME variable in the APEX_DATA_PARSER package.
Best Regards,