Support Article
sql.SQLSyntaxErrorException: ORA-00972: identifier is too long
SA-56371
Summary
SQLSyntaxErrorException displays in the alert logs.
Error Messages
[PegaWorkManager : 21] [ STANDARD] [ ] [ FPM:01.05] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 1 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", R.pyOriginalStrategy as "R_pyOriginalStrategy", R.Cost as "R_Cost", R.URI as "R_URI", R.pyMaxBudget as "R_pyMaxBudget", R.pyBehaviour as "R_pyBehaviour", R.CampaignID as "R_CampaignID", R.ControlGroupValidityStart as "R_ControlGroupValidityStart", R.pyOutcome as "R_pyOutcome", R.pyApplication as "R_pyApplication", R.pyStrategy as "R_pyStrategy", R.pyGroupID as "R_pyGroupID", R.pxDecisionTime as "R_pxDecisionTime", R.Status as "R_Status", R.ControlGroupValidityEnd as "R_ControlGroupValidityEnd", R.pyUnit as "R_pyUnit", R.pxFactID as "R_pxFactID", R.pyCustomerSubSegment as "R_pyCustomerSubSegment", R.pyCustomerSegment as "R_pyCustomerSegment", R.MktValue as "R_MktValue", R.pyLongitude as "R_pyLongitude", R.pyWeight as "R_pyWeight", R.pyComponent as "R_pyComponent", R.pyDirection as "R_pyDirection", R.ExpectedRevenue as "R_ExpectedRevenue", R.MktType as "R_MktType", R.pyTargetBudget as "R_pyTargetBudget", R.IPAddress as "R_IPAddress", R.pxOriginalRank as "R_pxOriginalRank", R.pyPreviousComponent as "R_pyPreviousComponent", R.ImageURL as "R_ImageURL", R.pyInteraction as "R_pyInteraction", R.pyOriginalInteraction as "R_pyOriginalInteraction", R.pxRank as "R_pxRank", R.BundleParent as "R_BundleParent", R.pyReason as "R_pyReason", R.pyLatitude as "R_pyLatitude", R.pxIdentifier as "R_pxIdentifier", R.pyIssue as "R_pyIssue", R.UserAgent as "R_UserAgent", R.pxDecisionReference as "R_pxDecisionReference", R.SerialNumber as "R_SerialNumber", R.DeviceType as "R_DeviceType", R.InboundTreatment as "R_InboundTreatment", R.pySubjectID as "R_pySubjectID", R.pyOrganization as "R_pyOrganization", R.pyExternalID as "R_pyExternalID", R.SMSTextContent as "R_SMSTextContent", R.pyOperator as "R_pyOperator", R.RevokedOutcome as "R_RevokedOutcome", R.StreetCode as "R_StreetCode", R.CommsTypeCode as "R_CommsTypeCode", R.ChannelIdentifier as "R_ChannelIdentifier", R.Currency as "R_Currency", R.TransreferenceID as "R_TransreferenceID", R.EventName as "R_EventName", R.StartDataTime as "R_StartDataTime", R.Priority as "R_Priority", R.SenderName as "R_SenderName", R.AppOS as "R_AppOS", R.DeceasedIndividual as "R_DeceasedIndividual", R.Var5 as "R_Var5", R.Var6 as "R_Var6", R.Var3 as "R_Var3", R.Var4 as "R_Var4", R.Var1 as "R_Var1", R.Var2 as "R_Var2", R.AlertAmount as "R_AlertAmount", R.Var9 as "R_Var9", R.Var8 as "R_Var8", R.Var7 as "R_Var7", R.ChannelUsageType as "R_ChannelUsageType", R.NotorietyAssessmentValue as "R_NotorietyAssessmentValue", R.CommsOccuranceID as "R_CommsOccuranceID", R.NotorietyDetailAsssesValue as "R_NotorietyDetailAsssesValue", R.ProviderCommunication as "R_ProviderCommunication", R.Condition as "R_Condition", R.InvolvedPartyMarkingType as "R_InvolvedPartyMarkingType", R.VarOpenText as "R_VarOpenText", R.VariableID as "R_VariableID", R.LegalCapacity as "R_LegalCapacity", R.Var15 as "R_Var15", R.Var14 as "R_Var14", R.Var13 as "R_Var13", R.Var12 as "R_Var12", R.Var19 as "R_Var19", R.Var18 as "R_Var18", R.Var17 as "R_Var17", R.Var16 as "R_Var16", R.SenderIDType as "R_SenderIDType", R.Var11 as "R_Var11", R.Var10 as "R_Var10", R.AlertType as "R_AlertType", R.CommsEventID as "R_CommsEventID", R.Var20 as "R_Var20", R.IBANNumber as "R_IBANNumber", R.CommsReferenceSystem as "R_CommsReferenceSystem", R.CommsTemplate as "R_CommsTemplate", R.IsChannelSMS as "R_IsChannelSMS", R.SegmentCategory as "R_SegmentCategory", R.CommunicationMessageType as "R_CommunicationMessageType", R.RequestCreateDate as "R_RequestCreateDate", R.SenderDepartment as "R_SenderDepartment", R.IsReplyAllowed as "R_IsReplyAllowed", R.IsChannelPN as "R_IsChannelPN", R.EndDataTime as "R_EndDataTime", R.LinkedConversationID as "R_LinkedConversationID", R.IsChannelEmail as "R_IsChannelEmail", R.AppType as "R_AppType", R.SenderID as "R_SenderID", R.TransactionDate as "R_TransactionDate", R.NotorietyAssessmentType as "R_NotorietyAssessmentType", R.Urgency as "R_Urgency", R.ConfidentialityClassification as "R_ConfidentialityClassification", R.Signature as "R_Signature", R.LinkedTargetID as "R_LinkedTargetID", R.IsChannelSCI as "R_IsChannelSCI", R.CommsTitle as "R_CommsTitle", R.CriteriaServiceId as "R_CriteriaServiceId", R.TransactionAmount as "R_TransactionAmount", R.CommsReferenceID as "R_CommsReferenceID", R.CommsTopic as "R_CommsTopic", R.Language as "R_Language", R.PNToken as "R_PNToken", R.VarOpenTextType as "R_VarOpenTextType", R.ConversationId as "R_ConversationId", R.ClientCategory as "R_ClientCategory", R.Importance as "R_Importance", R.SecondAddressLine as "R_SecondAddressLine", R.Direction as "R_Direction", R.CriteriaObject as "R_CriteriaObject", R.BalanceAmount as "R_BalanceAmount", R.CustomerID as "R_CustomerID", C.CUSTOMERID as "C_CUSTOMERID",C.FULLNAME as "C_FULLNAME",C.CLV_VALUE as "C_CLV_VALUE",C.AGE as "C_AGE",C.EMAIL as "C_EMAIL",C.PHONE_NUMBER as "C_PHONE_NUMBER",C.GENDER as "C_GENDER",C.CUSTOMER_TYPE as "C_CUSTOMER_TYPE",C.ADDRESS as "C_ADDRESS",C.REGION as "C_REGION",C.PARTITIONKEY as "C_PARTITIONKEY",C.RELATIONSHIP_START_DATE as "C_RELATIONSHIP_START_DATE",C.ARREARS_FLAG as "C_ARREARS_FLAG",C.PRODUCT50_USAGE as "C_PRODUCT50_USAGE",C.DATE_OF_LAST_INVOICE as "C_DATE_OF_LAST_INVOICE",C.LATITUDE as "C_LATITUDE",C.LONGITUDE as "C_LONGITUDE",C.INCOME as "C_INCOME",C.FACEBOOK as "C_FACEBOOK",C.GOOGLEPLUS as "C_GOOGLEPLUS",C.INSTAGRAM as "C_INSTAGRAM",C.SKYPE as "C_SKYPE",C.TWITTER as "C_TWITTER",C.PROFILE_IMAGE_PATH as "C_PROFILE_IMAGE_PATH",C.ACCOUNT_ID as "C_ACCOUNT_ID",C.ARREARS_PROPENSITY as "C_ARREARS_PROPENSITY",C.CHURN_SCORE as "C_CHURN_SCORE",C.DATE_OF_BIRTH as "C_DATE_OF_BIRTH",C.INVOICE_TOTAL_CURRENT as "C_INVOICE_TOTAL_CURRENT",C.POST_CODE as "C_POST_CODE",C.CLIENT_CATEGORY as "C_CLIENT_CATEGORY",C.NOTORIETY_ASSESSMENT_TYPE as "C_NOTORIETY_ASSESSMENT_TYPE",C.INVOLVED_PARTY_TYPE as "C_INVOLVED_PARTY_TYPE",C.INVOLVED_PARTY_MARKING_TYPE as "C_INVOLVED_PARTY_MARKING_TYPE",C.CHANNEL_USAGE_TYPE as "C_CHANNEL_USAGE_TYPE",C.CHANNEL_TYPE as "C_CHANNEL_TYPE",C.MESSAGE_TYPE as "C_MESSAGE_TYPE",C.CONTACT_POINT_USAGE_TYPE as "C_CONTACT_POINT_USAGE_TYPE",C.STREET_CODE as "C_STREET_CODE",C.DECEASED_INDIVIDUAL as "C_DECEASED_INDIVIDUAL",C.NOTORIETY_ASSESMENT_VALUE as "C_NOTORIETY_ASSESMENT_VALUE",C.NOTORIETY_DETAIL_ASSES_VAL as "C_NOTORIETY_DETAIL_ASSES_VAL",C.SECOND_ADDRESS_LINE as "C_SECOND_ADDRESS_LINE",C.LEGAL_CAPACITY as "C_LEGAL_CAPACITY",C.OLD_PHONE_NUMBER as "C_OLD_PHONE_NUMBER",C.APP_OS as "C_APP_OS",C.APP_TYPE as "C_APP_TYPE",C.PN_TOKEN as "C_PN_TOKEN",C.PN_OPT_OUT as "C_PN_OPT_OUT",C.CSIID as "C_CSIID",C.LANGUAGE as "C_LANGUAGE",C.INGID as "C_INGID",C.FIRSTNAME as "C_FIRSTNAME",C.LASTNAME as "C_LASTNAME",CS.Channel10AllPeriodCount as "CS_Channel10AllPeriodCount",CS.Channel10Daily as "CS_Channel10Daily",CS.Channel10LastUpdatedDate as "CS_Channel10LastUpdatedDate",CS.Channel10Monthly as "CS_Channel10Monthly",CS.Channel10Quarterly as "CS_Channel10Quarterly",CS.Channel10SemiMonthly as "CS_Channel10SemiMonthly",CS.Channel10Weekly as "CS_Channel10Weekly",CS.Channel10Yearly as "CS_Channel10Yearly",CS.Channel1AllPeriodCount as "CS_Channel1AllPeriodCount",CS.Channel1Daily as "CS_Channel1Daily",CS.Channel1LastUpdatedDate as "CS_Channel1LastUpdatedDate",CS.Channel1Monthly as "CS_Channel1Monthly",CS.Channel1Quarterly as "CS_Channel1Quarterly",CS.Channel1SemiMonthly as "CS_Channel1SemiMonthly",CS.Channel1Weekly as "CS_Channel1Weekly",CS.Channel1Yearly as "CS_Channel1Yearly",CS.Channel2AllPeriodCount as "CS_Channel2AllPeriodCount",CS.Channel2Daily as "CS_Channel2Daily",CS.Channel2LastUpdatedDate as "CS_Channel2LastUpdatedDate",CS.Channel2Monthly as "CS_Channel2Monthly",CS.Channel2Quarterly as "CS_Channel2Quarterly",CS.Channel2SemiMonthly as "CS_Channel2SemiMonthly",CS.Channel2Weekly as "CS_Channel2Weekly",CS.Channel2Yearly as "CS_Channel2Yearly",CS.Channel3AllPeriodCount as "CS_Channel3AllPeriodCount",CS.Channel3Daily as "CS_Channel3Daily",CS.Channel3LastUpdatedDate as "CS_Channel3LastUpdatedDate",CS.Channel3Monthly as "CS_Channel3Monthly",CS.Channel3Quarterly as "CS_Channel3Quarterly",CS.Channel3SemiMonthly as "CS_Channel3SemiMonthly",CS.Channel3Weekly as "CS_Channel3Weekly",CS.Channel3Yearly as "CS_Channel3Yearly",CS.Channel4AllPeriodCount as "CS_Channel4AllPeriodCount",CS.Channel4Daily as "CS_Channel4Daily",CS.Channel4LastUpdatedDate as "CS_Channel4LastUpdatedDate",CS.Channel4Monthly as "CS_Channel4Monthly",CS.Channel4Quarterly as "CS_Channel4Quarterly",CS.Channel4SemiMonthly as "CS_Channel4SemiMonthly",CS.Channel4Weekly as "CS_Channel4Weekly",CS.Channel4Yearly as "CS_Channel4Yearly",CS.Channel5AllPeriodCount as "CS_Channel5AllPeriodCount",CS.Channel5Daily as "CS_Channel5Daily",CS.Channel5LastUpdatedDate as "CS_Channel5LastUpdatedDate",CS.Channel5Monthly as "CS_Channel5Monthly",CS.Channel5Quarterly as "CS_Channel5Quarterly",CS.Channel5SemiMonthly as "CS_Channel5SemiMonthly",CS.Channel5Weekly as "CS_Channel5Weekly",CS.Channel5Yearly as "CS_Channel5Yearly",CS.Channel6AllPeriodCount as "CS_Channel6AllPeriodCount",CS.Channel6Daily as "CS_Channel6Daily",CS.Channel6LastUpdatedDate as "CS_Channel6LastUpdatedDate",CS.Channel6Monthly as "CS_Channel6Monthly",CS.Channel6Quarterly as "CS_Channel6Quarterly",CS.Channel6SemiMonthly as "CS_Channel6SemiMonthly",CS.Channel6Weekly as "CS_Channel6Weekly",CS.Channel6Yearly as "CS_Channel6Yearly",CS.Channel7AllPeriodCount as "CS_Channel7AllPeriodCount",CS.Channel7Daily as "CS_Channel7Daily",CS.Channel7LastUpdatedDate as "CS_Channel7LastUpdatedDate",CS.Channel7Monthly as "CS_Channel7Monthly",CS.Channel7Quarterly as "CS_Channel7Quarterly",CS.Channel7SemiMonthly as "CS_Channel7SemiMonthly",CS.Channel7Weekly as "CS_Channel7Weekly",CS.Channel7Yearly as "CS_Channel7Yearly",CS.Channel8AllPeriodCount as "CS_Channel8AllPeriodCount",CS.Channel8Daily as "CS_Channel8Daily",CS.Channel8LastUpdatedDate as "CS_Channel8LastUpdatedDate",CS.Channel8Monthly as "CS_Channel8Monthly",CS.Channel8Quarterly as "CS_Channel8Quarterly",CS.Channel8SemiMonthly as "CS_Channel8SemiMonthly",CS.Channel8Weekly as "CS_Channel8Weekly",CS.Channel8Yearly as "CS_Channel8Yearly",CS.Channel9AllPeriodCount as "CS_Channel9AllPeriodCount",CS.Channel9Daily as "CS_Channel9Daily",CS.Channel9LastUpdatedDate as "CS_Channel9LastUpdatedDate",CS.Channel9Monthly as "CS_Channel9Monthly",CS.Channel9Quarterly as "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 PEGXD.MKT_EVENT_P578 R INNER JOIN PEGXD.MKT_CUSTOMER C ON ( ( R.CustomerID = C.CUSTOMERID ) ) LEFT OUTER JOIN PEGXD.MKT_CONTACT_SUMMARY CS ON ( ( R.CustomerID = CS.CustomerID ) ) WHERE (R.FLOWSTATUS = 'Wait' AND R.WAITEXPIRATIONTIME <= current_timestamp ) ORDER BY R.CustomerID, R.BundleName, R.BundleParent desc, R.pxRank
java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long
Steps to Reproduce
Define a property between 28 to 31 characters.
Root Cause
An issue in the custom application code or rules.
When a property is created (for example, 29 characters), during the SQL alias generation, two more characters are appended. For example,
ConfidentialtiyClassification is 29 characters in length.
While generating the alias, Pega appends two more characters to it, that is, R_ConfidentialtiyClassification (31 characters). Oracle does not allow identifiers greater than 30 characters. Hence the exception, 'Identifier too long'.
Resolution
As a local-change, rename the property to less than 28 characters.
Published August 24, 2018 - 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.