Support Article

Segment Run throws SQLIntegrityConstraintViolationException

SA-28138

Summary



In Pega Marketing 7.21, Segment run was failing with ORA-01400 error.

Error Messages



<Sep 15, 2016 3:23:04 PM EDT> <Info> <Common> <BEA-000628> <Created "1" resources for pool "ExternalMKTDataDataSource", out of which "1" are available and "0" are unavailable.>
2016-09-15 15:23:04,406 [fault (self-tuning)'] [ STANDARD] [ ] [ Appname:01.01.01] (port_Definition_Segment.Action) ERROR - PopulateSegmentTable: SQL error while populating segment: There was a problem getting a list: code: 1400 SQLState: 23000 Message: ORA-01400: cannot insert NULL into ("NBAM"."MKTSEGTESTFORPEGA"."PARTITIONKEY")
DatabaseException caused by prior exception: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("NBAM"."MKTSEGTESTFORPEGA"."PARTITIONKEY") | SQL Code: 1400 | SQL State: 23000
SQL = INSERT INTO MKTSEGTESTFORPEGA (CustomerID, PARTITIONKEY) SELECT DISTINCT "PC0"."CUSTOMERID" AS "CustomerID" , "PC0"."PARTITIONKEY" AS "PARTITIONKEY" FROM NBAM.MKT_CUSTOMER "PC0" WHERE ( "PC0"."FULLNAME" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) )
2016-09-15 15:23:04,407 [fault (self-tuning)'] [ STANDARD] [ ] [ Appname:01.01.01] (port_Definition_Segment.Action) ERROR - Failure in processing segment: MKTSEGTESTFORPEGA - SQL error while populating segment: There was a problem getting a list: code: 1400 SQLState: 23000 Message: ORA-01400: cannot insert NULL into ("NBAM"."MKTSEGTESTFORPEGA"."PARTITIONKEY")
DatabaseException caused by prior exception: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("NBAM"."MKTSEGTESTFORPEGA"."PARTITIONKEY") | SQL Code: 1400 | SQL State: 23000



Steps to Reproduce



Create and run the Segment.


Root Cause



Since Partitioning option was enabled in the Marketing Application settings, PARTITIONKEY column also gets created along with the CustomerID column in the Segment table.

The PARTITIONKEY column has a not null constraint enabled by default.

User expects that the customer table (MKT_CUSTOMER) which has the PARTITIONKEY column to have the not null value.

If user do not have the Partitioning option enabled in the Marketing Application settings, then the PARTITIONKEY column is not used and is not created with the Segment.



Resolution



User is not advised to have a null value in the PARTITIONKEY column in the customer table.
(or)
​Disable the Partitioning option in Marketing Portal >Configuration >Application Settings.

Published September 19, 2016 - Updated August 23, 2017

Have a question? Get answers now.

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