Question

Oracle Error-6502 numeric or value error; how to determine which variable is too small

  • 18 June 2021
  • 9 replies
  • 1213 views

Userlevel 4
Badge +7

When printing Customer_Order_IVC_RPI (Report Invoice)  an Oracle Error 06502: PL/SQL: numeric or value error: character string buffer too small in report method [CUSTOMER_ORDER_IVC_RPI.Report Invoice] is generated.  The error occurs on only certain customers and for other customers there is no error. 

How can I track down the offending statement?    

I have tried Debug without success.  Can I enable an Oracle trace?  Would an Oracle trace help and what are the steps for setting up an Oracle trace in IFS?   Are there other options for determining the cause of the error?


This topic has been closed for comments

9 replies

Userlevel 7
Badge +28

This means you have a field on the report that has information in it that is too large for the field size.

Userlevel 4
Badge +7

Hello Shawn, 

What is the best way to determine which field is too small?

 

Userlevel 7
Badge +28

Well, that is the $1000 question.  There isn’t a great way to figure it out, especially if the error is very generic like this one seems to be.  But at least you know the report and you know that there are certain customers that it fails on, while others pass through as expected.  

First I would pull a list of all of the fields on the report so you know what you are dealing with.

Then you need a dataset of information that has the ones that work and some of the ones that don’t.  I bet you can eliminate 50% of the fields as they’ll have field info like dates and numbers that shouldn’t be an issue.  Then it is down to the text fields typically.  Which of the fields on the records that fail seem to have data that is quite a bit longer then the good ones?  This can get complicated if you are using double byte characters, so hopefully it is standard English only.

Finally, take the suspect fields and compare what the IFS native field allows for length and compare it to what the report allows for length of the same field.  I would bet there is a mismatch where a field is shorter on the report or in the query behind the report and it fails when the text string is too long.

These can be a bit tedious to isolate and find.

Userlevel 5
Badge +8


Hi @brantmb,

This is a generic error that could occur due to several reasons. All the scenarios are covered in the below artical:

https://community.ifs.com/technical-issues-101/error-numeric-or-value-error-character-string-buffer-too-small-8188

The most common scenario is when the system tries to assign a value to a variable where the value is larger than the maximum value the variable can hold/store.

By looking at the given information it is not possible to say the exact location where error triggers. CUSTOMER_ORDER_IVC_RPI database report file need to debug to findout the exact location. 

Once possible option is: can you please enable the debug window , take the server invoke from there and run it on the PL SQL Developer.  Then view the program source error stack. It will give you some hints to identify the exact place.


- Nilushi
 

Userlevel 4
Badge +7

Hi Nilushi, 

I understand the reason for the error message.  I was looking for a efficient way to determine which variable is too small.  The customer invoice report has hundreds of fields to evaluate and to isolate the offending field would be rather time consuming. 

 

Userlevel 5
Badge +8

Hi @brantmb ,

As i have informed my previous reply, have you tried out the below option:

Please enable the debug window , take the server invoke from there and run it on the PL SQL Developer. Then view the program source error stack. It will give you some hints to identify the exact place.

Userlevel 6
Badge +9

As stated above, the error message r 06502: PL/SQL: numeric or value error: character string buffer too small in report method is a very generic error message. This typically appears when there is a mismatch in the data type/ field length assigned cannot hold the values. Since you state that this only occurs for a limited number of customers it maybe due to the fact that their length exceeding the character length assigned in the database. The best case would be to debug this on PLSQL and backtracking the error stack to pinpoint the exact place where this comes.

 

Cheers.

Userlevel 4
Badge +5

Hi @brantmb 

Since the error occurs only for some customers, the issue could be with the customer specific data like customer name, addresses, any description related to delivery. Compare those fields with the working example.

-Dheynoshan

Userlevel 4
Badge +7

Hello All, 

Using PL/SQL, I was able to place the program in debug and after stepping through the program I was able to find the variable that was too small.    

Thanks for all the great ideas!

Best regards.