SQL Exception: ORA-01843: ORA-01438 with BIX
SummaryBusiness 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 MessagesERROR - 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 Reproduce1. 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 CauseA 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.
ResolutionMake the following change to the operating environment:
Alter database columns in target database for date properties to be of varchar2(8) type.
Published May 22, 2017 - Updated June 29, 2017