ORA-03113: end-of-file on communication channel

[AhmedGamall540]

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

[mbleron]

What's the database version please?

[ghost]

I've the same issue, but only for flat files. Reading XLSX-Files works without problems, but when i try to import flat files (tried it also with your example test_8k.csv), the connection gets lost.

I'll also get an ORA-600: ORA-00600: Interner Fehlercode, Argumente: [12442], [], [], [], [], [], [], [], [], [], [], []

But with this, i don't find any information. The Trace-File shows not much, but perhaps it will help you (including detailled version). It's an Oracle XE 18c.

` Trace file /opt/oracle/diag/rdbms/xe/XE/trace/XE_ora_29240.trc Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 Build label: RDBMS_18.4.0.0.0DBRU_LINUX.X64_181017 ORACLE_HOME: /opt/oracle/product/18c/dbhomeXE System name: Linux Node name: xxxx.xxxx.com Release: 3.10.0-1062.4.1.el7.x86_64 Version: #1 SMP Fri Oct 18 17:15:30 UTC 2019 Machine: x86_64 Instance name: XE Redo thread mounted by this instance: 1 Oracle process number: 58 Unix process pid: 29240, image: oracle@xxxx.xxxx.com

2020-01-27T11:16:22.997787+01:00 SESSION ID:(59.47144) 2020-01-27T11:16:22.997820+01:00 CLIENT ID:() 2020-01-27T11:16:22.997825+01:00 SERVICE NAME:(xepdb1) 2020-01-27T11:16:22.997829+01:00 MODULE NAME:(SQL Developer) 2020-01-27T11:16:22.997835+01:00 ACTION NAME:() 2020-01-27T11:16:22.997839+01:00 CLIENT DRIVER:(jdbcthin : 18.3.0.0.0) 2020-01-27T11:16:22.997843+01:00 CONTAINER ID:(3) 2020-01-27T11:16:22.997847+01:00

DDE: Problem Key 'ORA 600 [12442]' was flood controlled (0x6) (incident: 16837) ORA-00600: Interner Fehlercode, Argumente: [12442], [], [], [], [], [], [], [], [], [], [], [] `

That's all. Setting Debug to true doesn't help, because the connection gets lost before. Anything else i can try?

[mbleron]

Thanks for reporting this. You should find a more detailed trace file in directory /opt/oracle/diag/rdbms/xe/XE/incident. I'll try to reproduce the issue, but since it's an ORA-600 (i.e. Oracle internal error) I'm afraid I won't be able to do much.

[ghost]

Thanks for this information, but there is no useful information for me in the incident directory, and it's just Oracle XE, so no support available. But what i've found out: When i try to read the file using getCursor, it works, but only the first line will be returned. I'm trying to debug the function what's happening, but perhaps this information will help you.

Edit: to be more precise after some debugging: if i use a "wrong" lineseperator, i get using "getCursor" at least 1 line returned. Using the right lineseperator (tested using DUMP() - it's only chr(10) in my case) i don't get any result (but also no error). I've added some debug-messages and found out that "iterate_context" works correctly and returns a correct "cells" result. But this won't be returned...

Edit2: getCursor now works without any problems. Reason of not returning anything was an (hidden) error when converting the example of the timestamp because of different language settings (german installation). When i changed the month e.g. from OCT to OKT, everything worked fine. There is still the problem with getRows - the strange thing is - I've seen that getCursor calls getRows internally, and this works. But the direct call of getRows raises the ORA-600....

[ghost]

Found a working solution for me, but still don't know the reason. It looks like there is an error in the CLOB-Handling calling a table-function. If a don't use ExcelTable.getTextFile but instead use a direct "string", it works.

So i changed the CLOB-overload of getRows from "p_file IN CLOB" to "p_flatfilename IN varchar2" and do the file-import in the ExcelTableImpl just before the "tableStart" (hardcoded dir for test only):

p_file := ExcelTable.getTextFile('CSVDIR',p_flatfilename); ExcelTable.tableStart(...);

[mbleron]

Finally had time to install XE and reproduce the issue. Indeed, it seems Oracle is messing with ODCI routines at runtime.

For this particular issue, using an inline scalar subquery (instead of a simple parameter) appears to fix it, e.g. :

select t.* 
from ExcelTable.getRows(
       p_file => ( select to_clob(bfilename('XL_DATA_DIR','test_8k.csv'),nls_charset_id('AL32UTF8')) from dual )
     , p_cols => q'{
                   "C1"  number
                 , "C2"  varchar2(50)
                 , "C3"  varchar2(50)
                 , "C4"  number
                 , "C5"  date format 'DD/MM/YYYY HH24:MI:SS'
                 , "C6"  date format 'DD/MM/YYYY HH24:MI:SS'
                 , "C7"  timestamp(6) format 'DD-MON-YYYY HH.MI.SS.FF9 AM'
                 , "C8"  varchar2(10)
                 , "C9"  varchar2(10)
                 , "C10" varchar2(1000) 
                 }'
     , p_skip => 0
     , p_line_term => chr(10)
     , p_field_sep => ';'
     , p_text_qual => '"'
     ) t
; 

[ghost]

Thanks a lot - a simple workaround, but it works. I don't know why I haven't tested it this way...