mbleron / oracle Goto Github PK

I'm getting the following error when using the following code and xlsx file (i believe it's to do with the worksheet name input parameter)

TEST.xlsx

ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 701
ORA-06512: at "OLAP_META_DATA.EXCELTABLE", line 303
ORA-06512: at "OLAP_META_DATA.EXCELTABLE", line 323
ORA-06512: at "OLAP_META_DATA.EXCELTABLE", line 1190
ORA-06512: at "OLAP_META_DATA.EXCELTABLE", line 1220
ORA-06512: at "OLAP_META_DATA.EXCELTABLE", line 1380
ORA-06512: at "OLAP_META_DATA.EXCELTABLEIMPL", line 61
ORA-06512: at line 1

select t.*
from table(
       ExcelTable.getRows(
         ExcelTable.getFile('DAT_DIR','TEST.xlsx')
       , 'DATA'
       , q'{ "COMPANY_CODE"       varchar2(10)  column 'A'
           , "PROFIT_CENTER"      varchar2(50)  column 'B'    
           , "LEVEL_NO"           varchar2(10)  column 'C'
           , "LEVEL1"             varchar2(100) column 'D'
           , "LEVEL2"             varchar2(100) column 'E'
           , "LEVEL3"             varchar2(100) column 'F'
           , "LEVEL4"             varchar2(100) column 'G'
           , "LEVEL5"             varchar2(100) column 'H'
           , "GL_CODE"            varchar2(20)  column 'I'
           , "FISCAL_YEAR"        varchar2(10)  column 'J'
           , "POSTING_PERIOD"     varchar2(10)  column 'K'
           , "PERIOD_ACTUAL"      varchar2(20)  column 'L'
           , "ACTUAL_YTD"         varchar2(20)  column 'M'  }'
       , 'A2'
       )
     ) t;

Hi,
It is posible to get cell comment?

I receive the following error when attempting to compile ExcelTableImpl.tpb:

[Error] PLS-00306 (77: 5): PLS-00306: wrong number or types of arguments in call to 'TABLEFETCH'.

I am using Oracle version 11.2.0.4. The error is on the call to ExcelTable.tableFetch...

member function ODCITableFetch (
self in out ExcelTableImpl
, nrows in number
, rws out anydataset
)
return number
is
begin

ExcelTable.tableFetch(
  self.atype 
, self.ctx_id
, self.done
, nrows
, rws
);

return ODCIConst.SUCCESS;

end ODCITableFetch;

However tableFetch is defined as below:

procedure tableFetch (
p_type in out nocopy anytype
, p_ctx_id in out nocopy binary_integer
, nrows in number
, rws out nocopy anydataset
)
is
l_nrows number := least(nrows, fetch_size);
begin...

Regards,
John

I've been using functions compile and serialize to validate and convert some equations to SQL expressions. There is an issue where parentheses aren't preserved in some situations. See below.

Example input: x - (y + z)
Example output: X - Y + Z

Example 2 input: x / (y * z)
Example 2 output: X / Y * Z

For a fix that seems to work, I changed line 1690 from:
if st(j).prec < oprc or ( st(j).prec <= oprc and asso = 1 ) then

to:
if st(j).prec < oprc or ( st(j).prec <= oprc and ( asso = 1 or strval in ('/','-') ) ) then

There's probably a better solution. I tried changing the associativity for those operators, but that caused other issues as I expected.

Thanks for the great package!

Hi Team

I am getting this error while executing the query

select t.*
from table(
ExcelTable.getRows(
ExcelTable.getFile('C:\Oracle','ooxdata3.xlsx')
, 'DataSource'
, ' "SRNO" number
, "NAME" varchar2(10)
, "VAL" number
, "DT" date
, "SPARE1" varchar2(6)
, "SPARE2" varchar2(6)'
, 'A2'
)
) t
;
[Error] Execution (4: 10): ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "APPS.EXCELTABLE", line 1868

Requesting you to please help.

Thanks
Vardhan

ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.EXCELTABLE", line 1412
ORA-06512: at "SYS.EXCELTABLE", line 1464
ORA-06512: at "SYS.EXCELTABLE", line 1572
ORA-06512: at "SYS.EXCELTABLE", line 2034
ORA-06512: at "SYS.EXCELTABLEIMPL", line 60
ORA-06512: at line 1

When I am trying to run a select query I am getting this error

plcalc.serialize(plcalc.compile('declare(x:=1,y:=2) return x/y' ))
yields:
X := 1

The token parser for ExcelTable should be in its own package.
Possibly even in its own repository.

MK

create or replace procedure(v_file_name varchar2)
as
begin
insert into xxx
SELECT T.*
FROM TABLE (ExcelTable.getRows (
ExcelTable.getFile ('DATA_DIR', V_FILE_NAME)
,'Sheet1'
,' "Contract" varchar2(100)
, "BANK_ACCOUNT" varchar2(100)
, "CCID_Cashpool" varchar2(100)
, "LE_CURR" varchar2(100)
, "CURR" varchar2(5)
, "AMT" number ','A2') ) T ;

exception
when others then
dbms_output.put_line('In others of proc..');

end;

while compiling the procedure i am getting the following error.
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

Requesting you to please help me on the same.

Thanks
YV

Hi Ron,

This is very help full.But when i execute the below i'm getting error like "ORA-29913: error in executing ODCITABLESTART callout" .

There were three columns in the excel and trying to read the same with ExcelTable Package.

Query used:
select t.*
from table(
ExcelTable.getRows(
ExcelTable.getFile('FTP_IN','Test.xlsx')
, 'DataSource'
, ' "Country_Code" varchar2(10)
, "Person Number" number
, "Element_Name" varchar2(50) '
, 'A2'
)
) t
;

oracle导入命令expdp中有个参数remap_中的第一个参数为原用户、原表空间，数据导入的时候不是把dumpfile文件中的数据导入到目标表空间和目标用户中吗，那这个原用户和原表空间是不是有点鸡肋。