Support Article
SQL Exception: ORA-01843: ORA-01438 with BIX
SA-38355
Summary
Business Intelligence Exchange (BIX) is not Able To Extract Date Type Property and gives Is Not Valid Month error.
BIX is not able to extract just Date type property value to the target database table as it is giving below error in log file.
Error Messages
ERROR - Severe SQL Exception: ORA-01843: not a valid month java.sql.BatchUpdateException: ORA-01843: not a valid month
ERROR -Severe SQL Exception: ORA-01438: value larger than specified precision allowed for this column
Steps to Reproduce
1. Create a property of type Date in PRPC.
2. Create a BIX Extract rule which will include the above property and select its type as Date.
3. Create the target database table making the Oracle database column for this Date property as a TIMESTAMP database column.
4. Run the extract rule, and see ORA-01843: not a valid month error in the log file and the BIX extract fails.
5. Now, change the type of the target data base column to DATE type.
6. Re-run the extract rule again and now see ORA-01438: value larger than specified precision allowed for this column error and the BIX extract fails.
Root Cause
A defect or configuration issue in the operating environment.
As documented a date property within Pega should map to a varchar2(8) column within an Oracle database as Oracle does not have a pure date column type.
Both DATE and TIMESTAMP database column types include both a date and a timestamp value.
There was a bug in earlier versions of BIX which incorrectly mapped date properties in Pega to datetime column types in the database (Date or Timestamp in Oracle). This would result in incorrect date data when only looking at the date portion of the column data.
Since the Oracle database does not have a pure date column type varchar2(8) is used instead.
Resolution
Make the following change to the operating environment:
Alter database columns in target database for date properties to be of varchar2(8) type.
Published June 29, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.