Solved

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

  • 12 February 2021
  • 4 replies
  • 3327 views

Userlevel 1
Badge +3

In IFS 10/Aurena on the Change Request Form we had 8 custom bulk/multiline fields set to 500 characters each. Submitting a change request with these filled to that limit was not a problem.

It was then requested to increase these to the max allowed, and after looking around, it seemed to be 4000 characters is what we needed to set, due to limits in PL/SQL. Each Custom Field was changed to a 4000 character multiline and when synchronized the database was showing Varchar2(4000) as expected for each.

When filling out a new Change Request, and using just 2000 characters in each of the custom fields, an error was thrown and the Aurena debug log window shows the following:
 

09:49:52 TRACE - DATABASE {"origin":"DB","indentation":"11","time":"94786019","category":"FRAMEWORK","level":"ERROR","type":"Framework","text":"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"}

09:49:52 ERROR - PL/SQL: (Server Error) numeric or value error: character string buffer too small Database error occurred. Contact administrator. undefined

However, if I only filled in 3 of the custom fields, with 2000 characters each equaling 6000 characters, I was able to successfully save the Change Request and then, after the save, I could update the blank fields individually until all fields were filled in. So the size of each custom field does not seem to be the problem.

Is there another limit to how much data can be passed when initially saving a change request? If so is there a way to increase this so we can save with all the fields filled in?

Thanks…

 

Edit: I did a test where I used 1000 characters for each field and it errored. Then I reduced one of the fields to 796 and it saved, for a total of 7796 between all of them. When I created another Change Request with 7796 in the fields but increased a non-related field by one character it errored again.

icon

Best answer by Yasas Kasthuriarachchi 15 April 2021, 19:01

View original

This topic has been closed for comments

4 replies

Userlevel 3
Badge +10

I have experienced similar issues in other versions of IFS.  The problem is a result of a hardcoded message buffer size in the API/package. It limits the total amount of data in all fields that can be included in the update/write of the record. You may need to have the API/package modified.

Userlevel 6
Badge +15

Hi @ChaFerrellC,

Based on your testing scenarios, it seems to be something wrong with the handling of attr_ parameter (attribute string) of the New__ PROCEDURE (base PROCEDURE to insert data). The data type and size of attr_ parameter is normally interpreted as VARCHAR2(32000).

Could you check the following points?
1. Try to re-create the same from IFS Enterprise Explorer (to determine if this error is propagated from the common backend code).
2. If you get the the same error on IFS Enterprise Explorer, then extract the erroneous backend call from the debug console and view the error stack on PL/SQL developer to pin point the issue.
3. If you are unable to re-create the same from IFS Enterprise Explorer, then you might need to open the Aurena debug log window to find more info.
4. If there are any custom events and actions enabled using the columns which you have changed the length from 500 to 4000. You might need to refactor them as well.

Cheers !
Dhananjaya.

Userlevel 1
Badge +3

Hi @dhlelk thanks for responding.

I will look into reproducing this in IFS EE, however, I am not thinking it is the fields themselves that is the issue but, as @dmanuele mentioned, it seems to be more of a buffer size of the page as a whole.

I can save a new Change Request with just one of the 8 fields filled in with 4000 characters, leaving the others blank, without any issues. Does not matter which of these 8 fields I use like this.

Once I increase the total of the 8 fields, any combination, to greater than 7796 characters total, it errors. If I make the combo equal exactly 7796 characters it saves. However, with them totaling 7796 characters I can change something like the description field by adding one character than what I was using it will fail again.

Seems to indicate that the total number of characters on the page as a whole is causing the issue not the 8 fields we changed. They work individually but not as a whole, or in combination with each other.

We are investigating the buffer issue with our system admins and I will also looking into what you suggested with the IFS EE.

Userlevel 7
Badge +30

Refer the KBA cumulative of solutions : Update the KBA thread with new queries and solutions.