Hi @chathura_sml,

For more information, about the error refer 

.
Hope this helps!

Best Regards,
Bhagya

Cannot access the page

Copying the content:

ISSUE/QUESTION:

Why do I get Numeric or value error: character string buffer too small error in the application?

ENVIRONMENT: IFSAPP, Oracle, IFSCloud

AUDIENCE: ALL

RESOLUTION/ANSWER:

This is a generic error that could occur due to several reasons. The possible scenarios and what you need to check are listed below relative to the scenario.

Scenario 1

Numeric or value error: character string buffer too small error may occur if the size of the variable get exceeded from the maximum possible reach. You will need to check if the storage value declared on the variable is adequate to retrieve the output.

Scenario 2
Data type mismatches may also cause this error. For example, if the database type for a certain column is NUMBER and you are passing a String this error may occur. Avoid introducing variables manually by type and length and try referring the variables directly.

Example: if you want to get part description to a new variable, without using the declaration as new_variable_for_description varchar2(200); it would be better if you use table column reference directly as new_variable_for_description part_catalog_tab.description%type;

Scenario 3

If the database parameter NLS_LENGTH_SEMANTICS value is set to ‘Byte’ this error may occur as well. This parameter can be changed using SQL Developer tool (the parameter is set to Byte by default). If you compile packages setting this parameter value to 'BYTE' the error could occur.
In SQL Developer you can do the following to check on this.

1. Go to sql developer (Tools/Preferences/Database/NLS).
2. Change the value of NLS_LENGTH_SEMANTICS to ‘CHAR’ using command ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

3. Recompile the invalid packages
4. Check if any other package has compiled incorrectly using following query
   SELECT name, owner, 'CASE type WHEN 'PACKAGE' then 'PACKAGE ' || owner || '.' || name || ' COMPILE' WHEN 'PACKAGE BODY' then 'PACKAGE ' || owner || '.' || name || ' COMPILE BODY'  ELSE type || ' ' || owner || '.' || name || ' COMPILE'  END sql_stmt  FROM all_plsql_object_settings  WHERE owner in ('IFSINFO', 'IFSAPP')  AND nls_length_semantics = 'BYTE'  ORDER BY type
5. Check if any view has compiled incorrectly using following query
   select ut.table_name, ut.tablespace_name, ut.status, utc.COLUMN_NAME, utc.DATA_TYPE, utc.CHAR_USED  from user_tables ut, user_tab_cols utc  where utc.TABLE_NAME = ut.TABLE_NAME  and   utc.DATA_TYPE = 'VARCHAR2'  and   utc.CHAR_USED = 'B'  order by ut.table_name, utc.COLUMN_NAME; 
6. Recompile the any wrong packages using
   ALTER PACKAGE work_order_role_api COMPILE; 

Scenario 4

You may experience the error when populating data in a form. Check if error stack points to a method which returns a string of 4000 characters, which is being called inside a SQL SELECT query. Oracle SQL select has a limit of 4000 bytes and the above method make sure to return only a string which has maximum of 4000 characters by using substr() function.

The reason for this situation is that substr() will only consider characters (length()) and not bytes (lengthb()) but Oracle SQL select limit is of 4000 bytes (lengthb()). So when the string contains some multiple byte characters (special characters), even though they are in 2 or 3 bytes, it is still considered as 1 character and hence substr('string',1,4000) will return a string with 4000 characters but it can have more than 4000 bytes. Therefor the error comes due to the Oracle limitation.

A solution for this scenario would be to use substrb() instead of substr() which will consider bytes instead of characters when splitting the string.

Scenario 5

If a database table has mixed data, character and number, and it could be that later IFS versions restrict the data to one of the data types (both client and database code) and if so, this error may not appear for sometimes based on SQL select ordering criteria, but it can appear all of a sudden when similar data enters this database table as new records. In such situations, you may want to filter the old data with incorrect (as of now) data type and covert the old data to new data type as a data repair.

Scenario 6

You could get this error in trying to create a Execute Online SQL custom event action in IFS Cloud at the Aurena interface.  Database error occurred. Contact administrator ORA-06502: PL/SQL: numeric or value error: character string buffer too small when you have more that the possible number of characters defined.
This issue (Execute Online SQL custom event action in IFS Cloud at the Aurena interface) will be fixed via http://jira/browse/DXEXT-1635
Following workarounds are currently available for this issue.

1. Create a new event action with the final (modified) PL/SQL block & remove the existing
2. Add the content of the PL/SQL block into a separate PL/SQL procedure using a customization & call that procedure from the PL/SQL block here, so that the PL/SQL would contain less than 1000 characters. 

Hi @Bhagya Wickramasinghe,

I have checked all of the reason changes. however I couldn't found any errors as described here. Could you please update is there any tools to check those issues. 

Regards

Chathura