search

mbleron / ExcelTable

ExcelTable is an Oracle SQL interface to read Microsoft Excel files (.xlsx, .xlsm, .xlsb, .xls, .xml), ODF spreadsheet files (.ods) and flat files as external tables
MIT License
45 stars 12 forks source link

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT #43

Closed DBLangston closed 1 year ago

DBLangston commented 1 year ago

mbleron,

First, thanks for writing this code.

I am working on importing an Excel .XLSX file. The file is replaced with newer version everyday. ExcelTable was complied with no issues, I am using an Oracle 19c multitenant database with most recent quarterly patch. The pga_aggregate_limit is 12G, pga_aggregate_target is 6G, sga_max_size is 18G and physical memory is 60G.

I complied all objects (7 packages and 4 types) in one of the pluggable DB, in one of the non-sys schemas.

I can read .xlsx file with 1000 - 2500 rows with no issue, however the third-party supplied file has over 320,000 rows. When trying to access the larger file, I get the following error;

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT ORA-06512: at "LSA.EXCELTABLE", line 2458 ORA-06512: at "LSA.EXCELTABLE", line 2748 ORA-06512: at "LSA.EXCELTABLE", line 3157 ORA-06512: at "LSA.EXCELTABLE", line 4019 ORA-06512: at "LSA.EXCELTABLEIMPL", line 104 ORA-06512: at line 1

DBLangston

mbleron commented 1 year ago

Have you tried the streaming mode? (requires the Java bits installed though) p_method => ExcelTable.STREAM_READ

DBLangston commented 1 year ago

Thanks! That did the trick, however, I had to use "p_method => 1". I got an invalid parameter message otherwise.

Thanks again!