MAXIMO 7 on Oracle 11 allows invalid date year to be stored in the database - solved
NenadNesovanovic 270006JM7Q Visits (12668)
We encountered an issue in MAXIMO 7.5 with the Oracle 11 database, where system allowed user to store 5 digit years into database. Such invalid date values in the database with the year over 9999 caused errors when reading data or interfacing them to other systems.
We found similar issue already reported, but no permanent solution was available.
IV36705: MAXIMO DATE FIELDS (ALLOW > 4 LENGTH YEAR ENTRY)
There are several ways to reproduce this issue in the system:
· Enter PM with 2 million days frequency
· Setup PM at 10,000 km where Asset Meter average goes to 0.01 km per day
· Setup Customer Agreement in SP and enter 2 million months duration
Upon further investigation we discovered that this issue was due to an Oracle bug, which skips date year validation, if date is stored through JBDC bind variable as Date object.
Oracle JDBC Drivers release 126.96.36.199.0 production Readme.txt
BUG-7271519 Out of range Oracle date datatypes could be inserted into the database.
BUG-7249052 It was possible to construct invalid Oracle DATE values.
BUG-7243157 Raise an appropriate error when attempting to use a date with year exceeding 9999.
MAXIMO 7.5 is delivered with Oracle JDBC driver 188.8.131.52.0, and fix for this issue is provided in the Oracle JDBC driver 184.108.40.206.0 and higher up to 220.127.116.11.0.
In order to fix this issue, you will need to replace “oraclethin.jar” in the MAXIMO folder. First download the latest JDBC driver from Oracle website and then rename it to “oraclethin.jar” and the copy it to “max
Full details on how to reproduce this bug on Oracle 11g are provided below.
Step 1: create table TEST_DATE (TEST_DATE_COLUMN DATE);
Step 2: Create this JDBC class and compile it.
public class JdbcTest
public static void main(String args)
Connection conn = Driv
PreparedStatement ps = conn
Date dDate = new Date(12015,1,1); // Invalid Oracle Date
catch (Exception e)
Step 3: Run this class with the current MAXIMO application driver
JDBC driver version is 11.1
Step 4: Run select from the table and you get an error
ORA-01847: day of month must be between 1 and last day of month
01847. 00000 - "day of month must be between 1 and last day of month"
Step 5: Run the same class with the ORACLE JDBC5 standard driver
JDBC driver version is 18.104.22.168.0