Wednesday, October 26, 2022

The strange case of the REVERSE function

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Goal: reverse the order of the characters in a string:

select reverse('hello') s from dual;

exec dbms_output.put_line(reverse('hello'));
 
S    
-----
olleh


Error starting at line : 11 in command -
BEGIN dbms_output.put_line(reverse('hello')); END;
Error report -
ORA-06550: line 1, column 28:
PLS-00201: identifier 'REVERSE' must be declared
ORA-06550: line 1, column 7:
 

The REVERSE function exists only in the context of a SQL statement, therefore in order to execute it in a PL/SQL block you need to put it inside a SELECT ... FROM.

Interestingly, REVERSE is nowhere to be found in the SQL Reference but the function  seems to be available since at least Oracle 11g.

Another possibility is to write your own PL/SQL REVERSE function (but with a different name).

See message translations for PLS-00201 and search additional resources.

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

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