Thursday, May 12, 2022

Excel's "The file is corrupt and cannot be opened"

I wonder when Microsoft will be able to give some more clues about what Excel doesn't like in an XML file instead of this generic and useless message. 

 

It shouldn't be that difficult to write a log and show it to the user or at least display the first n errors, chances are that the remaining ones are of the same kind. 

Meanwhile, I had to use the binary search approach to iteratively cut in half the XML file containing some 3500 rows (roughly 250000 lines of XML) and find out at the 10th attempt and after more than an hour that a date value written as 15/12/0002 was the real reason for this message. 

It's also interesting to note that whilst this date value is certainly the result of a user entry error and a missing validation problem in the source application, the date value per se is not invalid at all, so I find that this is a twofold failure on Excel's part, because for Oracle it was perfectly acceptable.

By the way I also found out that Excel doesn't complain for the presence of spare "&" characters but some XML syntax checking programs actually do report these occurrences as errors.

So, in case you get "The file is corrupt and cannot be opened" error message, be prepared to spend some time if dealing with a large file in pursue of the offending value.

LOG(2, rows) should give you a good estimate of how many iterations are necessary to pinpoint the bad spot.

Sunday, November 14, 2021

Oracle XE21C and RHEL8 preinstall rpm wrong address

In case you are wondering why you can't download oracle database 21c preinstall file for Red Hat Linux 8 (RHEL8) using the instructions given in the documentation, the reason is quite simple, it's in a different repository.

Oracle Installation guide for XE21c says to download the preinstall file from here:

https://yum.oracle.com/repo/OracleLinux/OL8/latest/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm
however the file is here:
https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm
An attempt to download from the wrong URL will create a file containing a few bytes of garbage.

The URL for RHEL7 seems to be good instead (I mistakenly downloaded the file for RHEL7 at the beginning).

It's somewhat frustrating to see how people move things around lightheartedly after that the documentation has been published.

Hope it helps

Thursday, June 03, 2021

JSON_QUERY behavior in SQL vs PL/SQL (Oracle 12.2 only?)

Frankly speaking I can't say if this is expected behavior for JSON_QUERY but I find it somewhat inconsistent:

set serveroutput on
begin 
 dbms_output.put_line(json_query('X','$.test'));
end;
/
[] 
select json_query('X', '$.test') v from dual;
====== 
(null)

So, on Oracle 12.2 in PL/SQL the string returned is not null, whilst in SQL is null.

From a test I made later on Oracle 18c the anonymous PL/SQL block above returns null, so I am inclined to consider this a bug of Oracle 12.2.

It's also worth noting that adding NULL ON ERROR clause in Oracle 12.2 doesn't change the result, which is also another possible bug (or a variation of the same problem).

Eventually I chose to use ERROR ON ERROR then catch the exception and return null, because that should avoid the risk of inconsistent results in case the db is upgraded some time in the future.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles