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

Error on 5.0 version #22

Closed rass76 closed 4 years ago

rass76 commented 4 years ago

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)

rass76 commented 4 years ago

image

image

mbleron commented 4 years ago

What SQL query are you running?

rass76 commented 4 years ago

this one from examples:

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

of course I chnged DB dir ...

mbleron commented 4 years ago

I don't reproduce the issue on your db version. Could you try a fresh install? Drop everything and recreate.

rass76 commented 4 years ago

hmm okej, I tried to drop all object and reinstall but didnt' helped ( also tried to restart db). Only problem is to drop some java object, as you see on log from loadjava. I will try to go back to old version (2.x) which worked fine for us for one year. I have second idea to install 5.0 to diffent db user and let see, maybe some conflict with old install ...

rass76 commented 4 years ago

So I created fresh new users with clean schema, install exceltable was sucessfull including loadjava objects. But still I get same error when trying to execute example select.

Could I have this iisue ? https://odieweblog.wordpress.com/tag/pls-00307/

There is mention same error what I got: PLS-00307: too many declarations of 'ODCITABLEDESCRIBE' match this call

mbleron commented 4 years ago

Well, the error described in the blog post happens when we call getRows with bind variables instead of static strings. Is that the case here?

mbleron commented 4 years ago

Just got another idea. What's the value of the CURSOR_SHARING parameter?

select value 
from v$parameter
where name = 'cursor_sharing';

If it's FORCE or SIMILAR, then Oracle will try to variable-ize string literals in the query, resulting in that error.

rass76 commented 4 years ago

Well, the error described in the blog post happens when we call getRows with bind variables instead of static strings. Is that the case here?

it was just idea, I am callining your first simple example as wrote with sample_3.xlsx

rass76 commented 4 years ago

Just got another idea. What's the value of the CURSOR_SHARING parameter?

select value 
from v$parameter
where name = 'cursor_sharing';

If it's FORCE or SIMILAR, then Oracle will try to variable-ize string literals in the query, resulting in that error.

select value from v$parameter 3 where name = 'cursor_sharing' 4 ;

VALUE

SIMILAR

SQL>

mbleron commented 4 years ago

That's the explanation then.

It'll work if you set back CURSOR_SHARING to EXACT at system or session level : alter session set cursor_sharing = exact;

or, if you can't do that, you may also override it at query level with a hint :

select /*+ cursor_sharing_exact*/ t.*
from table( ...

Hope that helps.

rass76 commented 4 years ago

Yes ! That's it ... it works, perfect, thanks.

Now I must check why we have on our 12c RAC set similar and not exact what is default, similar is depredicated even on 11, interesting ...

mbleron commented 4 years ago

You're welcome. I'll add a word about this in the documentation.