Support Article
BIX extract causes BatchUpdateException and ORA-01843 error
Summary
BIX Extract rules were initially working in PRPC 6.3 SP1 but it fails after upgrade to Pega 7.1.7.
It seems like the date values are no longer supported in BIX and the system is throwing BatchUpdateException caused by ORA-01843 error.Error Messages
ORA-01843: not a valid month Steps to Reproduce
- Have an environment setup in PRPC 6.3 SP1 with extract rules having property of DateTime
- Upgrade that environment to Pega 7.1.7
- Run the Extract Rules which were created in 6x version.
Root Cause
Extract rules have a “DATE” Property mapped to them. So when one generates a DDL of DATE property in 6.2SP2 it is of type TIMESTAMP. But when we generate the DDL of DATE property in Pega 7.1.7, it is of type VARCHAR(8).
DDL of Extract rule in PRPC 6.3 SP1:
Create Table PegaSample (LastNoteType VARCHAR2(32),LastQuestionDateTime TIMESTAMP,pyCreatorID VARCHAR2(32),pyCalendar VARCHAR2(32),pxUpdateOperator VARCHAR2(32),pzInsKey VARCHAR2(128),pxRunIdentifier VARCHAR2(128),DateOnly TIMESTAMP, LastNoteDateTime TIMESTAMP,pyCustomer VARCHAR2(32),LastQuestion VARCHAR2(32),pxExtractDateTime TIMESTAMP);
DDL of Extract Rule in Pega 7.1.7:
/* The following CREATE statements should be un-commented before running this script if the destination tables do not already exist in the destination database */
/*Create Table PegaSample (pxUpdateOperator VARCHAR2(32),pyCreatorID VARCHAR2(32),pyDeadline TIMESTAMP,pyDueBy TIMESTAMP,pxExtractDateTime TIMESTAMP,pzInsKey VARCHAR2(256),pyCalendar VARCHAR2(32),pxUpdateDateTime TIMESTAMP,pyStart TIMESTAMP,pxCreateDateTime TIMESTAMP,pxExtractIdentifier VARCHAR2(256),pyCustomer VARCHAR2(32),DateOnly VARCHAR2(8));
Resolution
Install an HFix-23294 and then follow the below steps to resolve the issue:
1. Include a Date property which will mapped to a Dummy “DateTime” Property in the Extract Rule
- Include the DDL column for Date property as “TIMESTAMP” .
CREATE TABLE "PRPC6311"."PEGASAMPLE"
(
"PZINSKEY" VARCHAR2(256 BYTE),
"PXEXTRACTIDENTIFIER" VARCHAR2(256 BYTE),
"DATEONLY" TIMESTAMP (6),
"PXEXTRACTDATETIME" TIMESTAMP (6)
)
- Include a Declare Expression where we map the Date property to dateTime
- The prconfig.xml must have this env tag:
- Now run the extract rule to see the Time value getting extracted for old work objects even.
Published August 30, 2015 - 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.