ExcelTable 4.0.1 and Oracle XE 11.2.0.2

[ryouga82]

Hello Marc,
as described in the title I need some help to configure last version supporting Java on Oracle < 11.2.0.4
I have an instance 11g XE (I know it's quite older) and no problem using current version.
But I'd like to use Java features to import large xlsx file (> 20K) so I uninstalled that version and tried to install 4.0.1 but I had some problem with dependencies. Using last version of them I had compile errors so I tried this one:
ExcelCommons 0778ddb
MSUtilities 1cf944523338f146b05dc78791f04ab0c3d7818d
In that manner I have no compile errors but when I try to read a small xlsx file I have I/O error.
Tomorrow morning I can access to that env and copy error string.

Thanks

[mbleron]

Hi,
Unfortunately, 11g XE does not support Java.

[ryouga82]

Sorry, I didn't know. The error that I encountered was ORA-03113: end-of-file on communication channel. I'll find another way to import that file. Thanks

[mbleron]

How long does it take to read your 20K file using the default DOM_READ method? Do you get any error in the process?

[ryouga82]

About 5 min to select 2 of the 130 columns of that file and no errors.

SQL> SELECT t.*
2 FROM Table(
3 ExcelTable.getRows(
4 ExcelTable.getFile('DATA','Articoli.xlsx')
5 , 'Gateway'
6 , '"CODICE" VARCHAR2(255)
7 ,"ANNO" VARCHAR2(255)'
8 , 'A2' --skip prima riga
9 )
10 ) t;
19828 rows selected (5 m 52 s 579 ms)

But if I try to select all 130 columns I obtain ORA-01704: string literal too long (because all the table script is more than 4000 chars)

[ryouga82]

I tried that select statement twice and now I obtain:

ORA-04030: out of process memory when trying to allocate 16356 bytes (koh-kghu call ,kollalos2)
ORA-06512: at "NOLA.EXCELTABLE", line 2374
ORA-06512: at "NOLA.EXCELTABLE", line 2469
ORA-06512: at "NOLA.EXCELTABLE", line 2844
ORA-06512: at "NOLA.EXCELTABLE", line 3254
ORA-06512: at "NOLA.EXCELTABLE", line 4133
ORA-06512: at "NOLA.EXCELTABLEIMPL", line 104
ORA-06512: at line 1

I think it could be a limit of XE version

[mbleron]

Yes, the DOM parser used in the default method consumes a lot of memory, and indeed XE is limited too.

[ryouga82]

I managed to insert all 130 columns simply renaming them as A1, A2 so I can stay under 4000 chars (so no more ORA-01704)
SQL> INSERT INTO STG_ARTICOLI
2 SELECT t.*
3 FROM Table(
4 ExcelTable.getRows(
5 ExcelTable.getFile('DATA','Articoli.xlsx')
6 , 'Gateway'
7 , '"A1" VARCHAR2(255)
8 ,"A2" VARCHAR2(255)'
...
19828 rows selected (17 m 22 s 312 ms)

Thanks for your support, I know XE is a limited DBMS