Support Article

Campaign runs failing due to java.sql.SQLSyntaxErrorException

SA-33112

Summary



The following error occurs in the logs for a campaign on a daily basis.
"java.sql.SQLSyntaxErrorException: ORA-00904: "C"."PARTITIONKEY": invalid identifier"


Error Messages



2017-01-23 11:25:01,239 [ PegaRULES-Batch-10] [ STANDARD] [ <YOUR APP>:01.01.01] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 20 for database "externalmktdata" to the pool as it previously encountered the following error
User ID: System
Last SQL: SELECT R.FlowErrorDetail as "R_FlowErrorDetail", R.FlowStatus as "R_FlowStatus", R.LastFlowStep as "R_LastFlowStep", R.ParentGroup as "R_ParentGroup", R.ParentIssue as "R_ParentIssue", R.ParentOffer as "R_ParentOffer", R.ProgramKeyCode as "R_ProgramKeyCode", R.StatusWork as "R_StatusWork", R.TreatmentKeyCode as "R_TreatmentKeyCode", R.WaitExpirationTime as "R_WaitExpirationTime", R.pyPartitionKey as "R_pyPartitionKey", R.pyWorkID as "R_pyWorkID", R.pyOriginalComponent as "R_pyOriginalComponent", R.pxSegment as "R_pxSegment", R.pxPriority as "R_pxPriority", R.BundleType as "R_BundleType", R.IsOfferInProgress as "R_IsOfferInProgress", R.pxInteractionID as "R_pxInteractionID", R.pyTreatment as "R_pyTreatment", R.BundleHead as "R_BundleHead", R.KeyCode as "R_KeyCode", R.pyGroup as "R_pyGroup", R.pyDivision as "R_pyDivision", R.pyName as "R_pyName", R.BudgetedCost as "R_BudgetedCost", R.StartDate as "R_StartDate", R.ExpectedResponseRate as "R_ExpectedResponseRate", R.pxOutcomeTime as "R_pxOutcomeTime", R.pyPropensity as "R_pyPropensity", R.pyCategory as "R_pyCategory", R.Type as "R_Type", R.BundleName as "R_BundleName", R.pyChannel as "R_pyChannel", R.ClickThroughURL as "R_ClickThroughURL", R.Revenue as "R_Revenue", R.EndDate as "R_EndDate", R.pyApplicationVersion as "R_pyApplicationVersion",
...
"CS_Channel9Quarterly",CS.Channel9SemiMonthly as "CS_Channel9SemiMonthly",CS.Channel9Weekly as "CS_Channel9Weekly",CS.Channel9Yearly as "CS_Channel9Yearly",CS.CustomerID as "CS_CustomerID",CS.PartitionKey as "CS_PartitionKey" FROM PEGANBAM_EXTERNAL.BatchOutPR5941 R INNER JOIN PEGANBAM_EXTERNAL.MARKETING_SPINEC ON ( ( R.CustomerID = C.CASE_ID AND R.PARTITIONKEY = C.PARTITIONKEY ) ) LEFT OUTER JOIN PEGANBAM_EXTERNAL.MKT_CONTACT_SUMMARY CS ON ( ( R.CustomerID = CS.CustomerID AND R.PARTITIONKEY = CS.PARTITIONKEY ) ) WHERE (R.FLOWSTATUS = 'Wait' AND R.WAITEXPIRATIONTIME <= current_timestamp AND R.PARTITIONKEY = 7 AND C.PARTITIONKEY = 7) ORDER BY R.CustomerID, R.BundleName, R.BundleParentdesc, R.pxRank
java.sql.SQLSyntaxErrorException: ORA-00904: "C"."PARTITIONKEY": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:581)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6202)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5979)
at com.pega.decision.dsm.batch.RDBBatchReader.<init>(RDBBatchReader.java:73)
at com.pegarules.generated.createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.createSQLBasedIterator07_10_17(createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.java:131)
at com.pegarules.generated.createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.invoke(createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.java:107)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.resolveAndinvokeFunctionViaReflection(LibraryRuntime.java:133)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.invokeLibraryRuntime(LibraryRuntime.java:110)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeLibraryRuntime(Executable.java:9000)
at com.pega.pegarules.priv.generator.LibrarySupport.resolveAndInvokeFunctionViaReflection(LibrarySupport.java:181)
at com.pegarules.generated.pega_decisionengine_batchdecision.createSQLBasedIterator(pega_decisionengine_batchdecision.java:488)
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.step34_circum0(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:3327)
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.perform(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:753)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.step7_circum0(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:729)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.perform(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:191)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:721)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:602)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:945)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1259)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:901)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:670)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:745)
2017-01-23 11:25:01,247 [ PegaRULES-Batch-10] [ STANDARD] [ YOUR_APP:01.01.01] (fferBatch.System_Queue_.Action) ERROR - ProcessOfferBatch: Batch execution failed. java.lang.Throwable: com.pega.pegarules.pub.PRRuntimeException: Cannot list the database
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.step34_circum0(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:3331)
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.perform(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:753)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.step7_circum0(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:729)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.perform(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:191)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:721)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:602)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:945)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1259)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:901)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:670)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:745)


Steps to Reproduce



Run a Pega Marketing campaign


Root Cause



A defect or configuration issue in the operating environment . The marketing configuration was changed recently to introduce partitioning.
In the class db table the column is named differently to PARTITIONKEY. The error is coming from Offer Wait Processing agent and the queue items gets into broken state.

Resolution



Perform the following local-change:
Update the underlying table column and use PARTITIONKEY column to partition data.
Update the Pega Marketing custom class external mapping tab to assign PARTITIONKEY table column to .PartitionKey property.

Published January 31, 2017 - Updated April 25, 2017

Have a question? Get answers now.

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