Oracle Error ORA-01801 (9D,1801) with Acu4GL

0 Likes

This article provides a resolution to Oracle error ORA-01801 (9D,1801) when running Acu4GL.

Problem:

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.

Resolution:

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: 
Old KB# 14169
Comment List
Related
Recommended