Support Article

WriteToTable: ORA-12899 Records not processed

SA-38883

Summary



When user is saving a string with "," to DB, through DBOutputTemplate record fails and not moved from MKT_Outbount_Staging table.


Error Messages



(MKT_Data_Outbound_Queue.Action) ERROR - WriteToTable: ORA-12899: value too large for column "PEGXD"."FPM_SCI_COMMS_STAGING"."C" (actual: 46, maximum: 10)

log error:
2017-05-16 11:18:20,124 [PegaWorkManager : 29] [ STANDARD] [ ] [ FPM:01.04] (MKT_Data_Outbound_Queue.Action) ERROR - WriteToTable: ORA-12899: value too large for column "PEGXD"."FPM_SCI_COMMS_STAGING"."SEGMENTCATEGORY" (actual: 46, maximum: 10)


Steps to Reproduce



1. Include DBOutput template with three columns (say A, B, C).
2. Assume following are the values for three columns saved to DB through DBOutputTemplate:

A - "11111"
B - "This is for testing, Pega Marketing"
C - "2222"

3. Below is how it looks in Outbound Staging table, Each value will be separated by ",".

(A,B,C)(11111, "This is for testing, Pega Marketing",2222)

In this scenario, there is a limit for C (4 Bytes) And when user saves data, see a exception in log "Value is too large" for "C".

When there is a "," in string (B), it is considering ""This is for testing" as value for B and "Pega Marketing"" as value for C.

The records were stuck in Outboundstaging table and not processed.

----
Scenario 1:

ABC = “This is for testing, Pega Marketing”

Observation : This one fails

Scenario 2:

ABC = “Test, ing

Observation : This one fails

Scenario 3:

ABC = “This is for testing Pega Marketing”

Observation : This one passed.


Root Cause



A defect in Pegasystems’ code or rules

When there is comma or a special character in the column record that need to be inserted to the staging Output table from the Staging table, splitting of the text occurs.

Thus the portion of the text after the comma or special character is treated as a data fro the next column which can sometime exceed the length of this column and causes this DB exception.


Resolution



Apply the following local-change step:

1. Re save the
Activity : OutboundDBChild in to application ruleset.
This activity has a java step which splits the values by comma delimiter.

The split below does not ignore the delimiter within the column value.


_value = valPayLoad.split(",");

2. Change the above Java statement in to the below:

_value = valPayLoad.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)", -1); //New code added.



Published June 2, 2017 - Updated August 7, 2017

Have a question? Get answers now.

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