Issue/Introduction:
Error Message :
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "", line 37
When defining a substitution variable in Applications Manager that runs a procedure and the results of the procedure are greater than 100 characters you will receive an ORA-06502: PL/SQL: numeric or value error: character string buffer too small error similar to this:
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "<procedure name>", line 37
The error occurs because there is a 100 character or less limit on procedure results stored in a subvar. Procedure subvars were designed to allow for the use of quick SQL procedures which return a result with a short character length. We recommend that any SQL statement that takes a lengthy amount of time or returns a long result, should be run as a job and not as a subvar for better performance.
The AM documentation lists Rules to keep in mind when defining a procedure to call from a subvar (see link below). The rules do not mention that the result output should not exceed 100 characters in length. This should be added.
Executing Procedures in Dynamic Substitution Variables
Information regarding this limitation will be added to the “Rules” section for the Procedure subvars within the Applications Manager documentation.
Reproduction Steps:
- Create a procedure.
- Create a subvar that will use the procedure to retrieve information stored in the database.
- Create a job that has 2 conditions defined and will use the above subvars/procedure one that will return less than 100 characters and the other that will return more than 100 characters from the procedure.
- Run the job.
- The job will go into a DB ERROR status and in the RmiServer log you will see messages similar to the following:
06:00:43.504 rfp0: awapi3.my_logbook 4 msg: IN:OUT:VARCHAR2:java.lang.String:java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
awop_api.evaluate_procedure
ORA-06512: at line 1
ORA-06512: at "CT8LV.AWOP_API", line 1079
ORA-06512: at "CT8LV.AWOP_API", line 1100
ORA-06512: at "SYS.DBMS_SQL", line 1825
ORA-06512: at line 1
ORA-06512: at "<procedure name>", line 37
0 SQL: IN:VARCHAR2:java.lang.String:<procedure name>( :result )
1 :result: OUT:VARCHAR2::null
Cause:
Cause type:
By design
Root Cause: Procedure subvars unable to return a result greater than 100 characters.
Environment:
Release: AAMOS499000-8.0-Automic Applications Manager-OS400 Agent
Component:
Resolution:
1. Ensure the results returned by the procedure within the subvar are less than 100 characters.
2. If the results of the procedure are greater than 100 characters, run the procedure from a job and not as a subvar.
Applications Manager documentation has been updated.
Fix Status: No Fix
Additional Information:
Workaround :
N/A