Jobs going to DB Error right at kick-off

Document ID : KB000089832
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Jobs going to DB Error right at kick-off
Resolution:

Symptoms

Jobs when kicking off go into DB ERROR and display an Oracle error message:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Cause

Generally, these errors are seen when the job(s) kicking off use subvars, the names for which exceed the allotted 30 character limit that is allowed. Normally, this is seen when names for subvars are appended in a way that they have replacement values like {jobid} or the results of other SQL statements which have grown in size since they were first designed. As a result the name of the subvar is now longer than when it was originally created and is exceeding the 30 character limit.

2012-07-11 14:30:57 java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "APP_MANAGER702.AWAPI2", line 833

ORA-06512: at "APP_MANAGER702.AW5", line 2482

ORA-06512: at line 1

aw5.aw_condition_action

          0 jobid: IN:NUMERIC:java.math.BigDecimal:100020679

          1 condition_order: IN:NUMERIC:java.math.BigDecimal:3

          2 action: IN:VARCHAR2:java.lang.String:SET SUBVAR

          3 performed: IN:OUT:VARCHAR2:java.lang.String:N

          4 actionArg: IN:VARCHAR2:java.lang.String:#DATA_ACQ_NXT_CHN_REQ_100020674=20

          5 results: OUT:NUMERIC::null

          6 text: OUT:VARCHAR2::null

In the case above, the jobid is being appended to the subvar #DATA_ACQ_NXT_CHN_REQ_ which in turn causes the length of the subvar to go over 30 characters. The '#' symbol is counted towards the total length of the subvar as well.


Resolution

This requires shortening the name of the subvar so that its total length is under 30 characters. This can be done via a change in logic behind how the subvar is named.