This article provides a resolution to Oracle error ORA-01801 (9D,1801) when running Acu4GL.
This problem appears upon upgrading from runtime/Acu4GL Version 6.1 to Versions 7.x and 8.x. With Acu4GL Version 6.1, empty dates were converted in Oracle with a value of 00-DECEMBER-00. Although this behavior was fixed in release 6.2, it’s possible that old values are still stored in Oracle tables, so when runtime/Acu4GL Version 7.x or 8.x tries to read these date fields, an Oracle error ORA-01801 appears. ORA-01801 means that the date format is too long for the internal buffer.
One solution is to alter the Oracle session, calling the Acu tool sql.acu before opening an Oracle table, as shown here:
CALL "sql.acu" USING "ALTER SESSION SET NLS_LANGUAGE='AMERICAN'". CALL "sql.acu" USING "ALTER SESSION SET NLS_TERRITORY='AMERICA'". OPEN INPUT table-name
However, this method makes fields "read only."
If you try to modify these fields, a new Oracle error appears: 9D,1847 — day of month must be between 1 and last day of month.
The best solution is to modify these date fields directly, using Oracle SQL*Plus and a query like this:
UPDATE table-name SET date-field-name = '01-gen-01' WHERE date-field-name < '01-gen-01';
Incident Number: 2261267
00-DECEMBER-00 ORA-01801 9D,1801 ORA-01847 9D,1847
Date: Name: Description of change: Date: Name: Description of change: