Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

BIX extract causes BatchUpdateException and ORA-01843 error

SA-13412

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

  1. Have an environment setup in PRPC 6.3 SP1 with extract rules having property of DateTime
  2. Upgrade that environment to Pega 7.1.7
  3. 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


 
  1. 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)
  )
  1. Include a Declare Expression where we map the Date property to dateTime
 
  1. The prconfig.xml must have this env tag:
                      <env name="/compatibility/BIXdisableForwardChaining" value="False" />
 
  1. 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

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us