report error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small APEX

Question

I got this error report error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small on Oracle Apex v 20.2 When trying to parse data from .xlsx file to a report window when I increased the number of columns more than 85 columns , and my excel file got more than 300 columns still !

here is my query:

select line_number, col001, col002, col003, col004, col005, 
               col006, col007, col008, col009, col010,
                    col011,col012,col013,col014,col015,col016,col017,col018,col019,col020,col021,col022,col023,col024,col025,col026,col027,col028,col029,
                col030,col031,col032,col033,col034,col035,col036,col037,col038,col039,col040,col041,col042,col043,col044,col045,col046,col047,col048,col049,col050,col051  
                ,col052,col053,col054,col055,col056,col057,col058,col059,col060,col061,col062,col063,col064,col065,col066,col067,col068,col069,col070,
               col071,col072,col073,col074,col075,col076
               ,col078,col079,col080,col081,col082,col083,col084
               col085,col086,col087,col088,col089,col090 
                -- more columns (col011 to col300) can be selected here.


from apex_application_temp_files f, 
   table( apex_data_parser.parse(
              p_content                     => f.blob_content,
              p_add_headers_row             => 'Y',
              --
              p_xlsx_sheet_name             => :PX_XLSX_WORKSHEET,
              --
              p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
              p_file_name                   => f.filename ) ) p


where f.name = :PX_FILE

But If I select only 85 columns or less it works fine.

Any help !

Littlefoot Littlefoot · Accepted Answer · 2021-03-03T06:15:40

As far as I can tell, you can't do it as you hit the limits.

However, what you might do is one of the following options:

split the file into smaller chunks - 4 files with approx. 80 columns (as you said that 85 columns can be used). Each of them should contain the ID column which uniquely identifies each row, so that you could later "merge" them. That process probably requires 4 different database tables, as well as a little bit of programming efforts afterwards
save the file as a CSV (textual) file. Use SQL Loader to load it into the database. It doesn't have such a restriction and is VERY FAST so - you'd load it all in a single run.
- alternatively, you could use that file as a source for the external table. Note that this option requires access to the database server (while SQL Loader works with files on your own PC)

report error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small APEX

1 Answers