mbleron / exceltable Goto Github PK

Strings beginning with a single apostrophe ' are stored with a cell style having a quotePrefix attribute set to true.
The leading apostrophe itself is not stored, only the rest of the string.
ExcelTable does not handle this correctly.

Exception when loading a zero or negative value as a NUMBER(p) or NUMBER(p,s) :
ORA-06502: PL/SQL: numeric or value error

Using 2.3, I'm able to load large XLSX files without issue (albeit they do take some time), and also able to load smaller (10-15mb) XLS files also without issue. Is there any way to optimize for larger XLS files to enable loading? Time is not an issue (I'm trying to automate a monthly data load process), but I haven't seen the load complete even overnight. It doesn't error out, so not sure what is wrong. I don't have a sample XLS file that I can attach (it contains client data), unfortunately.

Hello,

I am using the following query:

SELECT t.*
  FROM TABLE(
          ExcelTable.getRows(
             p_file      => ExcelTable.getTextFile( 'TRAD_FOLDER', 'CiGenericErrMsgBundle.txt' )
           , p_cols      => q'{
                                "CODE"   varchar2(50)
                              , "ID"     varchar2(50)
                              , "MESSAGE" varchar2(256)'
                            }'
           , p_skip      => 0
           , p_line_term => CHR( 10 )
           , p_field_sep => CHR( 9 )
          )
       )t;

```to read a text file with tab delimited data.
I get the following error message:

ORA-00600: internal error code, arguments: [12442], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"

Hereafter the content of the file;
SQL_UNKNOWN_STR	SQL_UNKNOWN_STR	Unknown error "{0}" "{1}" "{2}"
SQL_QUERY_ERROR_SQLSERVER	QUERY_ERROR_SQLSERVER	Unexpected database error. Please ROLLBACK. \n ( Original database message: \n {0} )
VAL_PK	PK	{0}: Invalid Primary Key
VAL_TIMESTAMP	VAL_TIMESTAMP	{0}: Invalid Timestamp
VAL_INDEX	VAL_INDEX	{0}: Invalid index in collection
UNIQUE	UNIQUE	The "{0}" should be unique
NN	NN	Attribute {0} should not be null
CK	CK	Attribute {0} has an invalid value
FU_UPD_CPK	FU_UPD_CPK	Compound primary key cannot be modified
FU_UPD_CUK	FU_UPD_CUK	Compound unique key cannot be modified
FU_UPD_PK	FU_UPD_PK	Primary key cannot be modified
DUP_KEY	DUP_KEY	A(n) {0} with the code "{1}" already exists

Am I doing something wrong?
Thank you.

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

Hi!

It would be nice to be able to use a specific defined name of workbook instead of sheet+range to define a data region.

One function "getDefinedNames" will be enough for that.

I'm getting below error compiling xutl_offcrypto.pkb due to [Error] PLS-00201 (1319: 14): PLS-00201: identifier 'FILE2BLOB' must be declared. Please advise.

Thanks
Kishore

List of field positions sent to XUTL_FLATFILE must not include ordinal column.

First of all, thank you very much for such an excellent package. Sorry but I am something new to PLSQL. The truth is that I have not been able to figure out how I should use the getSheets function and I wanted to ask you for a code example of how I should use it. Maybe you could give me an example of code to use with one of the files that comes as an example (multisheet.xlsx for example). Thank you again

Hi,

I am using this code to read an .xls file but unable to do it.
Where am I going wrong? I am using the 5.0 version.

ORA-20731: Error at position 310477, expecting a [String] record
ORA-06512: at "RARFSTG2.XUTL_XLS", line 263
ORA-06512: at "RARFSTG2.XUTL_XLS", line 283
ORA-06512: at "RARFSTG2.XUTL_XLS", line 740
ORA-06512: at "RARFSTG2.XUTL_XLS", line 953
ORA-06512: at "RARFSTG2.XUTL_XLS", line 1498
ORA-06512: at "RARFSTG2.EXCELTABLE", line 4360
ORA-06512: at "RARFSTG2.EXCELTABLEIMPL", line 140

SELECT t.*
FROM Table(
ExcelTable.getRows(
ExcelTable.getFile('TEST'123,'tstxl90.xls')
, 'Unit Info - RENEWABLE'
, ' "COL1" varchar2(200)
, "COL2" varchar2(200)
, "COL3" varchar2(200)
, "COL4" varchar2(200)
, "COL5" varchar2(200)
, "COL6" varchar2(200)'
)
) t
;

Hello,

I used a lot ExcelTable to transform Excel files (.xlsx) into Oracle Views. Now I am using the same "template" to transform a text file into a view and I obtain some weird results.

I am using the latest code of ExcelTable and Oracle 19.3.0.0.0.

I attached a text file (lables.txt) that allow to simulate the problem and the definition of the view using ExcelTable (v_label.txt).

I use TAB as field separator and LF as line terminator.
labels.txt
v_label.txt

The following images show the problem. Excel.png shows the content of labels.txt in Excel. SQLDeveloper.png and SQLDeveloper.full.png shows the content of the view in SQLDeveloper (the same columns as Excel and also an extra computed column in the view).

As you can see the CONTROL_TYPE and ID columns is wrong for some lines (truncated for the second line and completely wrong -seems to have the previous lines value- for 6th and 9th line).

Am I doing something wrong in the definition of the view or is ExcelTable problem?

Thank you.
Best regards

Under Oracle 12c generates error during creating jar exceldbtools-1.6.jar

Error while creating jar java/lib/sjsxp-1.0.2.jar
ORA-06550:... 25:
PLS-00302 component 'FINISH_LOADING_JAR' must be declared

Hello Marc,

I am using Oracle 19 and the cursor_sharing parameter is set to exact.
I am trying to use ExcelTable.getRows and to specify the sheet using a function in a package. I obtain the folowing error:

ORA-06550: line 4, column 13:
PLS-00307: too many declarations of 'ODCITABLEDESCRIBE' match this call
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

If I use a hardcoded value everything works.
Best regards

I am trying to run the loadData function with the following:

declare

ctx GDRDBA.ExcelTable.DMLContext;
nrows integer;

begin
dbms_output.put_line( '1');
ctx := GDRDBA.ExcelTable.createDMLContext('T544');
dbms_output.put_line( '2');
GDRDBA.ExcelTable.mapColumnWithDefault(ctx, p_col_name => 'VALID_DATE', p_default => sysdate);
GDRDBA.ExcelTable.mapColumn(ctx, p_col_name => 'CURRENCY_CODE', p_col_ref => 'A');
GDRDBA.ExcelTable.mapColumn(ctx, p_col_name => 'CONVERSION_RATE', p_col_ref => 'G');
dbms_output.put_line( '3');
nrows :=
GDRDBA.ExcelTable.loadData(
p_ctx => ctx
, p_file => GDRDBA.ExcelTable.getFile('EXCEL_FILE_DIR','FX Rates Feb 2021.xlsx')
, p_sheet => 'query (2)'
, p_method => GDRDBA.ExcelTable.STREAM_READ
, p_dml_type => GDRDBA.ExcelTable.DML_INSERT
);

dbms_output.put_line(nrows || ' rows inserted.');

end;

When I run this on my database I get the following error:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.ExceptionInInitializerError
ORA-06512: at "GDRDBA.EXCELTABLE", line 2026
ORA-06512: at "GDRDBA.EXCELTABLE", line 2754
ORA-06512: at "GDRDBA.EXCELTABLE", line 3157
ORA-06512: at "GDRDBA.EXCELTABLE", line 5019
ORA-06512: at "GDRDBA.EXCELTABLE", line 5043

Can anyone help me out to figure out why I am getting this uncaught Exception?

Thanks

Mike

I am trying to implement this solution for my company.
The first test I was trying was with the loadData function

declare

ctx GDRDBA.ExcelTable.DMLContext;
nrows integer;

begin
dbms_output.put_line( '1');
ctx := GDRDBA.ExcelTable.createDMLContext('T544');
dbms_output.put_line( '2');
GDRDBA.ExcelTable.mapColumnWithDefault(ctx, p_col_name => 'VALID_DATE', p_default => sysdate);
GDRDBA.ExcelTable.mapColumn(ctx, p_col_name => 'CURRENCY_CODE', p_col_ref => 'A');
GDRDBA.ExcelTable.mapColumn(ctx, p_col_name => 'CONVERSION_RATE', p_col_ref => 'G');
dbms_output.put_line( '3');
nrows :=
GDRDBA.ExcelTable.loadData(
p_ctx => ctx
, p_file => GDRDBA.ExcelTable.getFile('EXCEL_FILE_DIR','FX Rates Feb 2021.xlsx')
, p_sheet => 'query (2)'
, p_method => GDRDBA.ExcelTable.STREAM_READ
, p_dml_type => GDRDBA.ExcelTable.DML_INSERT
);

dbms_output.put_line(nrows || ' rows inserted.');

end;

When I run this on my Test database I get the following:

ORA-29540: class db/office/spreadsheet/ReadContext does not exist
ORA-06512: at "GDRDBA.EXCELTABLE", line 2026
ORA-06512: at "GDRDBA.EXCELTABLE", line 2754
ORA-06512: at "GDRDBA.EXCELTABLE", line 3157
ORA-06512: at "GDRDBA.EXCELTABLE", line 5019
ORA-06512: at "GDRDBA.EXCELTABLE", line 5043
ORA-06512: at line 14

I checked the install log and see that the ReadContext class was created and I also see this class in the SYS schema.
I had mu Hosting DBA's grant PUBLIC execute on all of these classes as well.

The database I am using is:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

So I had my Hosting DBA's install exceldbtools-1.6.jar
From the README i think this is only jar that needs installed

So I am confused as to why I cannot find the java class when I see it in the database.
Any help would be greatly appreciated.

Thanks,

Mike

While importing the Data from XSLX files the data where the cell contains a Date is getting imported as NULL

E.x. of the data columns in XLSX

Product Name Lot Details Date Of import Date of Packing
Face Masks BoxABC 16-Jul-2015 n/a
Face Masks BoxXYZ 16-Jul-2016 n/a
Face Masks Box123 16-Jul-2017 n/a
Face Masks Box456 16-Jul-2018 n/a

Code for Import

INSERT INTO TDL_CLIENT_DATA (PRODUCT_NAME, LOT_NAME, DATE_IMPORT, PACK_DATE)
SELECT t.*
FROM Table(
ExcelTable.getRows(ExcelTable.getFile('DMP_DIR','Face_Masks.xlsx')
, 'Lot Details',
'"COL1" VARCHAR2(2000),
"COL2" VARCHAR2(2000),
"COL3" VARCHAR2(2000),
"COL4" VARCHAR2(2000)'
)
) t
;

if i change the column to date format then i get the error

INSERT INTO TDL_CLIENT_DATA (PRODUCT_NAME, LOT_NAME, DATE_IMPORT, PACK_DATE)
SELECT t.*
FROM Table(
ExcelTable.getRows(ExcelTable.getFile('DMP_DIR','Face_Masks.xlsx')
, 'Lot Details',
'"COL1" VARCHAR2(2000),
"COL2" VARCHAR2(2000),
"COL3" Date format 'DD-MMM-YYYY',
"COL4" Date format 'DD-MMM-YYYY''
)
) t
;

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

Any help would be appreciated

Hello,

After I installed 5.0 version I am getting this error, when I try to selact sample_3.xml.
DB version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

ORA-06550: line 4, column 13:
PLS-00307: too many declarations of 'ODCITABLEDESCRIBE' match this call
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored

install.sql pass sucesfully load jav show some errros.

Loadjava show this:

creating : jar exceldbtools-1.6.jar
loading : jar exceldbtools-1.6.jar
creating : resource META-INF/MANIFEST.MF
loading : resource META-INF/MANIFEST.MF
Error while creating resource META-INF/MANIFEST.MF
ORA-29537: class or resource cannot be created or dropped directly
ORA-06512: at line 1

identical: db/office/spreadsheet/odf/ODFCell
identical: db/office/spreadsheet/odf/ODFValueType
creating : class db/office/spreadsheet/CellReader
loading : class db/office/spreadsheet/CellReader
Error while creating class db/office/spreadsheet/CellReader
ORA-29537: class or resource cannot be created or dropped directly
ORA-06512: at line 1

identical: db/office/spreadsheet/Sheet
identical: db/office/spreadsheet/ICellType
creating : class db/office/spreadsheet/Row
loading : class db/office/spreadsheet/Row
Error while creating class db/office/spreadsheet/Row
ORA-29537: class or resource cannot be created or dropped directly
ORA-06512: at line 1

identical: db/office/spreadsheet/CellReaderException
creating : class db/office/spreadsheet/CellRef
loading : class db/office/spreadsheet/CellRef
Error while creating class db/office/spreadsheet/CellRef
ORA-29537: class or resource cannot be created or dropped directly
ORA-06512: at line 1

creating : class db/office/spreadsheet/Cell
loading : class db/office/spreadsheet/Cell
Error while creating class db/office/spreadsheet/Cell
ORA-29537: class or resource cannot be created or dropped directly
ORA-06512: at line 1

creating : class db/office/spreadsheet/ReadContext
loading : class db/office/spreadsheet/ReadContext
Error while creating class db/office/spreadsheet/ReadContext
ORA-29537: class or resource cannot be created or dropped directly
ORA-06512: at line 1

identical: db/office/crypto/BlowfishImpl
identical: db/office/spreadsheet/odf/ODFCellReaderImpl
identical: db/office/spreadsheet/odf/SAXDocumentSerializer
identical: db/office/spreadsheet/oox/OOXCellReaderImpl
identical: db/office/spreadsheet/oox/OOXCellType
identical: db/office/spreadsheet/oox/OOXCell
identical: db/office/spreadsheet/oox/SharedStringsHandler
identical: db/office/spreadsheet/oox/OOXCommentReader
skipping : class db/office/spreadsheet/odf/ODFCell
skipping : class db/office/spreadsheet/odf/ODFValueType
skipping : class db/office/spreadsheet/Sheet
skipping : class db/office/spreadsheet/ICellType
skipping : class db/office/spreadsheet/CellReaderException
skipping : class db/office/crypto/BlowfishImpl
skipping : class db/office/spreadsheet/odf/ODFCellReaderImpl
errors : class db/office/spreadsheet/odf/ODFCellReaderImpl
ORA-29552: verification warning: java.lang.IncompatibleClassChangeError: Implementing class

skipping : class db/office/spreadsheet/odf/SAXDocumentSerializer
skipping : class db/office/spreadsheet/oox/OOXCellReaderImpl
errors : class db/office/spreadsheet/oox/OOXCellReaderImpl
ORA-29552: verification warning: java.lang.IncompatibleClassChangeError: Implementing class

skipping : class db/office/spreadsheet/oox/OOXCellType
skipping : class db/office/spreadsheet/oox/OOXCell
skipping : class db/office/spreadsheet/oox/SharedStringsHandler
skipping : class db/office/spreadsheet/oox/OOXCommentReader
The following operations failed
resource META-INF/MANIFEST.MF: creation (createFailed)
class db/office/spreadsheet/CellReader: creation (createFailed)
class db/office/spreadsheet/Row: creation (createFailed)
class db/office/spreadsheet/CellRef: creation (createFailed)
class db/office/spreadsheet/Cell: creation (createFailed)
class db/office/spreadsheet/ReadContext: creation (createFailed)

Hi there,

I have 64 columns in Xlsx and 3 laks rows in a file, how can I access it? Kindly help?

When using getRows with method DOM_READ to load data from an Excel file, empty cells are filled with the value from the last non-emtpy cell. This continues until a new non-empty cell is encountered. When using STREAM_READ instead of DOM_READ, empty cells remain empty.

SELECT ROWNUM xlsx_rownum
, t.*
FROM TABLE(ExcelTable.getRows(ExcelTable.getFile('LOAD_DIR', 'ImportData.xlsx')
, 'TableX'
, ' "Source" varchar2(1000 BYTE) column ''A''
, "Name" varchar2(40 BYTE) column ''B''
, "Unit" varchar2(40 BYTE) column ''C''
, "Amount" varchar2(40 BYTE) column ''D'' '
, 'A1:D27'
--, 1 -- Adding 1 for method STREAM_READ fixes handling of empty cells
)
) t ;

I don't know the sheet name of the Excel file so I need to get it dynamically. I have tried the following but it returns no results:
SELECT *
FROM exceltable.getrows(p_blob_data, '.*',
'"C1" VARCHAR2(50),
"SHEET_NAME" varchar2(31) for metadata (sheet_name)')
WHERE SHEET_IDX = 1;

SELECT *
  FROM exceltable.getrows(p_blob_data, '.*',
                           '"C1" VARCHAR2(50),
                           "SHEET_NAME" varchar2(31) for metadata (sheet_name)');

This one will return the sheet name:
CURSOR c_xls_getsheet1 (p_blob_data BLOB)
IS
SELECT *
FROM exceltable.getrows(p_blob_data, '.*',
'"C1" VARCHAR2(50),
"SHEET_NAME" varchar2(31) for metadata (sheet_name)',
'4:4');

If I have multiple sheets, I haven't found a way to get the sheet name successfully.

When I run a script to test demo files, it shows the error

ORA-20722: Error at position 1 : unexpected symbol '' instead of ''

Then show error message ORA-06512 of EXCELTABLE at line 704. 1077, 1090, 1219, 1230, 1242, 2217. And EXCELTABLEIMPL at line 16

Can you suggest me how to fix this issue?

Arintaphon

Some of the Excel files I had to process contained so much data that it was spread across multiple sheets.

It would be nice to have an option to say that the value for p_sheet was actually a Regular Expression for the Sheet name.

At the same time, the FOR METADATA () syntax should support SHEET_NAME and SHEET_INDEX.

MK

Hello, we used exceltable package a few week ago and everything was fine. During this period, our oracle admins applied some patch or etc. and now we get an oracle error ORA-04088 which is connected with trigger checking grants to PUBLIC. I don't know why something should grant privileges to PUBLIC, but it seems that error emerges on this row - "open l_rc for l_query using p_file, p_method, p_password;" in first getCursor function in EXCELTABLE package. We use ORACLE DB 12.2. Do you have any idea where is granting privileges used and why emerges this error? Thank you in advance.

1. This is part of our SQL code:
   declare
   piv_excel_name ext_ds_upt_kalendar_pro_kl.nazev_souboru%TYPE := 'Kalendar.xlsx';
   piv_nazev_listu ext_ds_upt_kalendar_pro_kl.nazev_listu%TYPE := 'Plan';
   cv_db_adresar CONSTANT VARCHAR2(16) := 'DB_DIR';
   lvr_data SYS_REFCURSOR;

begin
lvr_data :=
ExcelTable.getCursor(
p_file => ExcelTable.getFile(cv_db_adresar, piv_excel_name)
, p_sheet => piv_nazev_listu
, p_cols => '"A2" VARCHAR2(100) column ''A'''
, p_range => 'A2:A2'
);
end;

2. This is whole error message:
   Error report -
   ORA-04088: error during execution of trigger 'APPLDBA_P.BEFORE_GRANT_PUBLIC'
   ORA-00604: error occurred at recursive SQL level 3
   ORA-20997: Public grants on data schema objects not allowed
   ORA-06512: on line 23
   ORA-06512: on "EXT_STAGE.EXCELTABLE", line 4087
   ORA-06512: on line 11

4088. 00000 - "error during execution of trigger '%s.%s'"
      *Cause: A runtime error occurred during execution of a trigger.
      *Action: Check the triggers which were involved in the operation.

3. This is the trigger mentioned above:
   create or replace TRIGGER appldba_p.before_grant_public BEFORE GRANT ON DATABASE
   declare
   vLst ora_name_list_t;
   vCnt int;

function is_authorized(p_owner varchar2,p_grantor varchar2) return varchar2 is
vRet varchar2(1);
begin
select decode(max(profile),'DAT_USER_PROFILE','F','T') into vRet from dba_users where username=p_owner;
if vRet='F' then
select decode(count(*),0,'F','T') into vRet from dba_role_privs where granted_role='DBA' and grantee=p_grantor;
end if;
return vRet;
end;

begin
if ora_dict_obj_name is null then
return;
end if;

vCnt:=ora_grantee(vLst);
for i in 1..nvl(vCnt,0) loop
if vLst(i)='PUBLIC' and is_authorized(ora_dict_obj_owner,ora_login_user)='F' then
raise_application_error(-20997,'Public grants on data schema objects not allowed');
end if;
end loop;
end;

Not sure if this is a bug/issue or an install issue.
But I couldn't find a user forum to post to, or the authors email.
If there is a better location to post this please let me know and I will post there.

Installing the following :
ExcelTable 2.3.2 (last commit October 28, 2018)
CDFReader (last commit April 1, 2018)
OfficeCrypto (last commit August 24, 2018)

My oracle version - Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Granted execute on sys.dbms_crypto to user qms_user
All objects built using userid qms_user per order in readme with no errors.

@xutl_cdf.pks
@xutl_cdf.pkb
@xutl_offcrypto.pks
@xutl_offcrypto.pkb
@ExcelTableCell.tps
@ExcelTableCellList.tps
@xutl_xls.pks
@xutl_xls.pkb
@xutl_xlsb.pks
@xutl_xlsb.pkb
@ExcelTableImpl.tps
@ExcelTable.pks
@ExcelTable.pkb
@ExcelTableImpl.tpb

I did not perform the java install because I was only interested in the ExcelTable.getRows functionality to replace the following :

CREATE TABLE NewTable as (SELECT * FROM External_file_as_table);
The current external file is a tab delimited text file which is being replace with a xlsx file.
(hence the need for ExcelTable)

Using the Examples section of the readme file I moved the sample spreadsheet sample_3.xlsx to a directory named DAT_DIR and then ran the following sample code from the readme as qms_user:
(changing the readme directory of XL_DATA_DIR to my directory of DAT_DIR)

select t.*
from table(
ExcelTable.getRows(
ExcelTable.getFile('DAT_DIR','sample_3.xlsx')
, 'DataSource'
, ' "SRNO" number
, "NAME" varchar2(10)
, "VAL" number
, "DT" date
, "SPARE1" varchar2(6)
, "SPARE2" varchar2(6)'
, 'A2'
)
) t
;

And received the following error :

ORA-06550: line 4, column 24:
PLS-00302: component 'EXCELTABLEIMPL' must be declared
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

I shorted the test script trying to validate the install. I ran the following :

select ExcelTable.getFile('DAT_DIR','sample_3.xlsx') from dual;

The result was (BLOB) as expected. So I feel that at least a portion of the install was successful.

My first instinct was that the getRows function was not defined in the ExcelTable.pkb package body source.
But then I realized that the getRows function in the ExcelTable.pks package header source implements ExcelTableImpl.

I'm assuming this is an install issue on my part but I couldn't find a user forum to check for help.
Help from anyone would be greatly appreciated.

Thanks,
Roy

hi mbleron, we are trying to load a excel file into oracle database table using ExcelTable.getRows and ExcelTable.getFile. It was working fine until our client changed format cells from text to general and custom mm/dd/yy to date *3/14/2012. with the new change it is filling the null column values with previous column value. Could you please check?

ExcelTable cannot read .xls files encrypted using RC4 CryptoAPI encryption, which corresponds to EncryptionVersion 2.2, 3.2 or 4.2 (according to RC4 CryptoAPI Encryption Header).

when trying to run the sample
select t.*
from table(
ExcelTable.getRows(
ExcelTable.getFile('DIR_TEST','sample_3.xlsx')
, 'DataSource'
, ' "SRNO" number
, "NAME" varchar2(10)
, "VAL" number
, "DT" date
, "SPARE1" varchar2(6)
, "SPARE2" varchar2(6)'
, 'A2'
)
) t
;

I get the following error on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ORA-06530: Reference to uninitialized composite
ORA-06512: at "CLOUD_EE.EXCELTABLE", line 4067
ORA-06512: at "CLOUD_EE.EXCELTABLEIMPL", line 140
06530. 00000 - "Reference to uninitialized composite"
*Cause: An object, LOB, or other composite was referenced as a
left hand side without having been initialized.
*Action: Initialize the composite with an appropriate constructor
or whole-object assignment.

I have a problem. When i execute the sentence select, i don't received records. Say '0 records return'. What hapen? i don't found other messages. Thank.

Hi, this is very usefull parser for XLS/XLSX file, but I got an error, and can't find the reason.
When I try to load .XLS files, some files - parsing got successed, but some files - got error (actually no error, session just freezing and dropped without error in some minutes without return any data)
It looks like codepage issue or something like it, but I really can't fix it.
Can you help me, please?
DB: Oracle 12.1

1. Load and try to parse file (http://www.atsenergo.ru/dload/retail/20190801/20190910_MOSENERG_PMOSENER_082019_gtp_1st_stage.xls)
2. using next source
   select t.sheetidx sheet_nr, t.cellrow row_nr, t.cellcol || t.cellrow nm_cell, sys.anydata.gettypename(t.celldata) as cell_type, case sys.anydata.gettypename(t.celldata) when 'SYS.VARCHAR2' then sys.anydata.accessvarchar2(t.celldata) when 'SYS.NUMBER' then to_char(sys.anydata.accessnumber(t.celldata), 'FM99999990D00999') when 'SYS.DATE' then to_char(sys.anydata.accessdate(t.celldata), 'DD-MON-YYYY HH24:MI:SS') end as cell_value from table(exceltable.getcells(p_file => :p_vl_file, p_sheet => sys.anydata.convertvarchar2('Лист1'), p_cols => '"A" varchar2(4000), "B" varchar2(4000), "C" varchar2(4000), "D" varchar2(4000), "E" varchar2(4000), "F" varchar2(4000) ')) t

Excel application - can open and edit this file, without any errors. And when I edit and save file via Excel application as .xls or .xlsx - source above can parse it easy...
But when I try to parse file from link above - it always freeze and dropped.

Hello,

I got the following errors for the package xutl_offcrypto:

Avertissement : Corps de package crÚÚ avec erreurs de compilation.

SQL> show error
Erreurs pour PACKAGE BODY XUTL_OFFCRYPTO :

LINE/COL ERROR

1391/5 PL/SQL: Statement ignored
1391/21 PLS-00302: Le composant 'NEW_FILE' doit Ûtre dÚclarÚ
1393/5 PL/SQL: Statement ignored
1393/14 PLS-00302: Le composant 'ADD_STREAM' doit Ûtre dÚclarÚ
1394/5 PL/SQL: Statement ignored
1394/14 PLS-00302: Le composant 'ADD_STREAM' doit Ûtre dÚclarÚ
1395/5 PL/SQL: Statement ignored
1395/14 PLS-00302: Le composant 'ADD_STREAM' doit Ûtre dÚclarÚ
1396/5 PL/SQL: Statement ignored
1396/14 PLS-00302: Le composant 'ADD_STREAM' doit Ûtre dÚclarÚ
1398/5 PL/SQL: Statement ignored

LINE/COL ERROR

1398/14 PLS-00302: Le composant 'ADD_STREAM' doit Ûtre dÚclarÚ
1399/5 PL/SQL: Statement ignored
1399/14 PLS-00302: Le composant 'ADD_STREAM' doit Ûtre dÚclarÚ
1400/5 PL/SQL: Statement ignored
1400/24 PLS-00302: Le composant 'GET_FILE' doit Ûtre dÚclarÚ

Best regards,

Hi,

In this example we have to write sheet name.
SELECT t.*
FROM TABLE (exceltable.getrows ( (SELECT l.file_data
FROM apps.fnd_lobs l
WHERE file_id = 505202),
'data',
' "COL1" number
, "COL2" varchar2(100)
, "COL3" number
, "COL4" date
, "COL5" varchar2(100)
, "COL6" varchar2(100)
, "COL7" varchar2(100)
')) t
WHERE col1 = '7'

What are we doing if we don't know the page name? Can we dynamically fetch the page name?

Best regards.

Hello,
I was using extensively ExcelTable to manipulate some text files as Oracle views. I discover recently that performing a join with such view does not work always as expected. I don’t know if I face a limit of ExcelTable or some bug (in ExcelTable or maybe in Oracle). I work with Oracle 19.3.0 and ExcelTable 5.0 version. The database nls_length_semantics.
Hereafter, an example to understand the problem.
I have defined the following view based on the file messages.txt placed into a folder named TRAD (the zip file attached contains the definition of all the objects and the files necessary to create the test case)
excel_table.zip

CREATE OR REPLACE VIEW V_MESSAGE
AS
   WITH EXCEL_FILE
      AS(
         SELECT t.*
           FROM TABLE(
                   ExcelTable.getRows(
                      p_file      =>(
                                       SELECT ExcelTable.getTextFile(
                                                 p_directory => 'TRAD'
                                               , p_filename  => 'messages.txt' )
                                         FROM DUAL
                                    )
                    , p_cols      => q'{
  "BUNDLE"  VARCHAR2( 100 )
, "IDX"     VARCHAR2( 10 )
, "KEY"     VARCHAR2( 100 )
, "LABEL"   VARCHAR2( 100 )
, "MESSAGE" VARCHAR2( 1000 )
}'
                    , p_skip      => 0
                    , p_line_term => CHR( 10 )
                    , p_field_sep => CHR( 9 )
                   )
                )t
      )
   SELECT BUNDLE
        , IDX
        , KEY
        , LABEL
        , MESSAGE
     FROM EXCEL_FILE e
WITH READ ONLY CONSTRAINT RO_V_MESSAGE;

The resulting view columns are:

I was a little bit surprised to see that the column sizes are expressed in BYTE. I expected sizes 4 times smaller without BYTE/CHAR or with CHAR.
The view has around 15900 rows and is working well. Hereafter, a query on the view and its result (that will help to show afterwards the problem encountered).

select *
  from V_MESSAGE
 where BUNDLE = 'MenuStructureBundle'
 order by KEY asc;

The results are correct.

I have problems when I join the view V_MESSAGE with another one, V_MENU_DATA. This view is presenting data from the table REM_MODULE in a hierarchical way.
Here are the columns of the V_MENU_DATA view:

and those of the table REM_MODULE:

The V_MENU_DATA view has around 3600 rows and is working well. Hereafter, a query on the view and its result (that will help to show afterwards the problem encountered).

select *
  from V_MENU_DATA
 order by MENU_NAME asc;

And now the query performing the join (and a variant of it) that does not work:

SELECT mdata.MENU_NAME
     , mdata.MENU_TYPE
     , mdata.PARENT_MENU
     , mdata.SYSTEM_MENU
     , mdata.MENU_LEVEL
     , mes.MESSAGE 
  FROM V_MENU_DATA mdata
     , V_MESSAGE mes 
 WHERE mdata.MENU_NAME = mes.KEY
   AND BUNDLE = 'MenuStructureBundle';

Hereafter the result. The results are obviously wrong for the message column (coming from the view based on ExcelTable). 3 times the same value throughout the whole result set!!!

I obtain the same result if I write the query a little bit different:
SELECT mdata.MENU_NAME
     , mdata.MENU_TYPE
     , mdata.PARENT_MENU
     , mdata.SYSTEM_MENU
     , mdata.MENU_LEVEL
     , mes.MESSAGE 
  FROM V_MENU_DATA mdata
     , ( select KEY, MESSAGE from V_MESSAGE where BUNDLE = 'MenuStructureBundle' ) mes 
 WHERE mdata.MENU_NAME = mes.KEY;

I observed that the results are correct if I join the V_MESSAGE view with the REM_MODULE table

SELECT mdata.CWMO_CODE
     , mdata.CWMO_TYPE
     , mdata.CWMO_PARENT
     , mes.MESSAGE 
  FROM REM_MODULE mdata
     , V_MESSAGE mes 
 WHERE mdata.CWMO_CODE = mes.KEY
   AND BUNDLE = 'MenuStructureBundle'
ORDER BY 1;

Am I doing something wrong ? Can you help me to overcome the problem?

Thank you very much,
Adrian Boangiu

Is there a way to include static values coming from PLSQL variables as part of the new DML API INSERT operation?

For example

DECLARE
    ctx         ExcelTable.DMLContext;
    nrows       INTEGER;
    user_email  VARCHAR2(100);
BEGIN
    -- Set variables
    ctx := ExcelTable.createDMLContext('TMP_SAMPLE2');
    user_email := '[email protected]';

    ExcelTable.mapColumn(ctx, p_col_name => 'NAME',         p_col_ref => 'B');
    ExcelTable.mapColumn(ctx, p_col_name => 'VAL',          p_col_ref => 'C');
    ExcelTable.mapColumn(ctx, p_col_name => 'CREATED_BY',   /* a way to referece variable user_email*/);

    nrows := ExcelTable.loadData(
        p_ctx      => ctx
        , p_file     => ExcelTable.getFile('XL_DATA_DIR','sample_2.xlsx')
        , p_sheet    => 'DataSource'
        , p_dml_type => ExcelTable.DML_INSERT
    );
END;
/

No result.
PS It Works. Forgot ExcelTable.useSheetPattern(true);

Hello, we are able to load small file,
in case we would like to use big file with this code

select * from table( ExcelTable.getRows( ExcelTable.getFile('BI','bigfile.xlsx') , 'data' , q'{ "ID" number , "FIRST_NAME" varchar2(15) , "LAST_NAME" varchar2(20) , "EMAIL" varchar2(30) , "GENDER" varchar2(10) , "IP_ADDRESS" varchar2(16) , "COMMENT" varchar2(4000) , "IMAGE" varchar2(4000) , "DT" date format 'DD/MM/YYYY' }' , 'A2' , 1 ) ) t ;

we have this issue

ORA-29532: Java call terminated by uncaught Java exception: javax.xml.stream.FactoryConfigurationError: java.security.AccessControlException: the Permission (java.lang.RuntimePermission getClassLoader) has not been granted to BI. The PL/SQL to grant this is dbms_java.grant_permission( 'BI', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' ) ORA-06512: at "BI.EXCELTABLE", line 1443 ORA-06512: at "BI.EXCELTABLE", line 1971 ORA-06512: at "BI.EXCELTABLE", line 2146 ORA-06512: at "BI.EXCELTABLE", line 2649 ORA-06512: at "BI.EXCELTABLEIMPL", line 60 ORA-06512: at line 1 29532. 00000 - "Java call terminated by uncaught Java exception: %s" *Cause: A Java exception or error was signaled and could not be resolved by the Java code. *Action: Modify Java code, if this behavior is not intended.

can you please look on it and help us?
We are using oracle 12c

thank you

Hi Marc,

I've been using your ExcelTable for a little while now. Calling getRawCells on an XLS file, the empty cells aren't returning any value. It's like they're getting skipped over. Do you have pointers for this scenario.

Thank you,

Chris

Erreur commençant à la ligne: 1 de la commande -
declare

ctx ExcelTable.DMLContext;
nrows integer;

begin

ctx := ExcelTable.createDMLContext('TMP_SAMPLE2');

ExcelTable.mapColumn(ctx, p_col_name => 'ID', p_col_ref => 'A');
ExcelTable.mapColumn(ctx, p_col_name => 'NAME', p_col_ref => 'B');
ExcelTable.mapColumn(ctx, p_col_name => 'VAL', p_col_ref => 'C');

nrows :=
ExcelTable.loadData(
p_ctx => ctx
, p_file => ExcelTable.getFile('STAT_AMAT','sample_3.xlsx')
, p_sheet => 'DataSource'
, p_method => ExcelTable.STREAM_READ
, p_dml_type => ExcelTable.DML_INSERT
);

dbms_output.put_line(nrows || ' rows inserted.');

end;
Rapport d'erreur -
ORA-29540: classe db/office/spreadsheet/ReadContext inexistante
ORA-06512: à "AMATEUR.EXCELTABLE", ligne 1608
ORA-06512: à "AMATEUR.EXCELTABLE", ligne 2126
ORA-06512: à "AMATEUR.EXCELTABLE", ligne 2304
ORA-06512: à "AMATEUR.EXCELTABLE", ligne 3481
ORA-06512: à ligne 14
29540. 00000 - "class %s does not exist"
*Cause: Java method execution failed to find a class with the indicated name.
*Action: Correct the name or add the missing Java class.

I tried to use the following select in a procedure but I got

select /*+ cursor_sharing_exact */ t."Schema" schema,t."Table Name" table_name,t."Partition Name" partition_name,
t."Partition High Value Date" high_value_date,
t."Tablespace Name" tablespace_name,
t."Retention" retention
from table(
ExcelTable.getRows(
ExcelTable.getFile('EXCELFILES','s1.xlsx')
, 'To_be_archived'
, ' "Schema" varchar2(30)
, "Table Name" varchar2(30)
, "Partition Name" varchar2(30)
, "Partition High Value Date" date
, "Tablespace Name" varchar2(30)
, "Retention" varchar2(30)'
, 'A8'
)
) t;
Error(40,10): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

Hello,

where I try to parse excel with more then 100k rows I got message below.
I found on good that I need to increase:
=>Increace the size of VM NodeStack in XmlXslVMCreate().

but not able to find solution to increase it.

Thanks,
Rasto.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00664: VM Node-Stack overflow
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 743
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 771
ORA-06512: at "BI.EXCELTABLE", line 1957
ORA-06512: at "BI.EXCELTABLE", line 2146
ORA-06512: at "BI.EXCELTABLE", line 2649
ORA-06512: at "BI.EXCELTABLEIMPL", line 60
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

DML API :
Default value defined via mapColumn (or mapColumnWithDefault) procedure is not applied when the cell is empty.

when i execute this select (select t.srno
, t.name
, t.content
, length(t.content) as content_length
from table(
ExcelTable.getRows(
p_file => ExcelTable.getFile('MYCSV','crypto2003.xls')
, p_sheet => 'DataSource'
, p_cols => ' "SRNO" number
, "NAME" varchar2(10)
, "CONTENT" clob'
, p_range => '1:91'
, p_method => null
, p_password => 'pass123'
)
) t
;
)
this ora is occured ORA-03113: end-of-file on communication channel

Hi Marc,

Attached is the actual file I am trying to read with ExcelTable.
This looks similar to the error that you fixed last time. It's with RT_CONTINUE.

This is the command I used to read the file.

SELECT t.*
FROM Table(
ExcelTable.getRows(
exceltable.getFile('TEST','19INR0073_RENEWABLE_FORM.xls')
,ExcelTableSheetList('Unit Info - RENEWABLE')
,' "COL1" varchar2(200 char)
, "COL2" varchar2(1 char)
, "COL3" varchar2(200 char)
, "COL4" varchar2(200 char)
, "COL5" varchar2(200 char)
, "COL6" varchar2(200 char)
, "COL7" varchar2(200 char)
, "COL8" varchar2(200 char)
, "COL9" varchar2(200 char)
, "COL10" date'
, '9:10',1
)
) t
;

I am getting this error stack:

ORA-20731: Error at position 969133, expecting a [Continue] record
ORA-06512: at "RARFSTG2.XUTL_XLS", line 263
ORA-06512: at "RARFSTG2.XUTL_XLS", line 283
ORA-06512: at "RARFSTG2.XUTL_XLS", line 479
ORA-06512: at "RARFSTG2.XUTL_XLS", line 696
ORA-06512: at "RARFSTG2.XUTL_XLS", line 714
ORA-06512: at "RARFSTG2.XUTL_XLS", line 1012
ORA-06512: at "RARFSTG2.XUTL_XLS", line 1408
ORA-06512: at "RARFSTG2.XUTL_XLS", line 1444
ORA-06512: at "RARFSTG2.EXCELTABLE", line 2812
ORA-06512: at "RARFSTG2.EXCELTABLE", line 3164
ORA-06512: at "RARFSTG2.EXCELTABLE", line 4019
ORA-06512: at "RARFSTG2.EXCELTABLEIMPL", line 124
ORA-06512: at line 1

Hello,
The new MSUtility does not contain anymore CDFReader and thus the installation script fails.
Previously MSUtilities was different in ExcelGen and ExcelTable (one contained CDFReader the other CDFManager). Now, there is only one MSUtilities and it contains only CDFmanager.

Best regards

Hi

I uploaded sample_3.xlsx into a table xx_temp_files and ran below query in TOAD SQL editor.

SELECT x.*
FROM xx_temp_files t,
TABLE (ExcelTable.getRows (t.blob_content,
'DataSource',
' "SRNO" number
, "NAME" varchar2(10)
, "VAL" number
, "DT" date
, "SPARE1" varchar2(6)
, "SPARE2" varchar2(6)',
'A2')) x
WHERE t.id = 10154484

It results in below error. My database version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0. Please advise.

ORA-03113: end-of-file on communication channel
Process ID: 23816
Session ID: 213 Serial number: 13

Thanks
Kishore

Hi,
First, thank you for this very useful program.
Just want to ask if passing a dynamic value to "p_cols" parameter is accepted, I encounter this error when I pass a dynamic value to "p_cols". Error(90,5): PL/SQL: ORA-20722: Error at position 1 : unexpected symbol '' instead of ''

ex: insert into cut_batchpay_gtmp (
select t.*
from table(
exceltable.getrows(
p_file => v_data
--, p_cols => p_column
, p_cols => q'{"TRAN_NO" VARCHAR2(20), "MAIN_ACCT_NO" VARCHAR2(20), "BENEFICIARY_NAME" VARCHAR2(150), "BENEFICIARY_ACCOUNT" VARCHAR2(20), "REFERENCE_NO" VARCHAR2(20), "TRAN_AMOUNT" NUMBER}'
, p_skip => 0
, p_line_term => v_eol_pattern
, p_field_sep => v_field_separator
)
) t
);

Thanks again.

Cris Tabardilla

Hi there,

thank you very much for this really useful program!
I'd like to suggest an enhancement: transforing the datatype of "p_cols" to CLOB.
Long lists of columns with somewhat long names hit the VARCHAR2 length limit.

Best wishes
Christian

As per ECMA-376 §22.9.2.19, Excel strings are stored as ST_Xstring which supports escaped invalid XML characters, i.e. control characters [0x00-0x1F] minus 0x09, 0x0A and 0x0D.
ExcelTable does not escape back such characters when reading string cells.