Numeric or value error: character string buffer too small
Best answer by Sahan Udana
This is a one possible explanation of this error can be occur.
"numeric or value error: character string buffer too small" error can occur if the size of the variable get exceeded. If this is not the scenario could be a probable cause for the issue.
If the database parameter NLS_LENGTH_SEMANTICS if this value is set to ‘Byte’ this error will occur. This parameter can be mostly change if we are using sql developer tool to which this parameter is set to default to Byte. If we compile packages setting this parameter value to 'BYTE' error could occur.
In sql developer we can do the following to check this. Go to sql developer (Tools/Preferences/Database/NLS). Change it to ‘CHAR’ .
to change incorrect value this needs to be used:
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
(Check image1)
To fix the issue need to recompile the invalid packages after changing the parameter to 'CHAR'.
To check any other package’s compiles incorrectly run this 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;
To check Views
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;
How to Fix it?
Need to recompile the wrong packages .
Ex: ALTER PACKAGE work_order_role_api COMPILE;
This is a one possible explanation of this error can be occur.
"numeric or value error: character string buffer too small" error can occur if the size of the variable get exceeded. If this is not the scenario could be a probable cause for the issue.
If the database parameter NLS_LENGTH_SEMANTICS if this value is set to ‘Byte’ this error will occur. This parameter can be mostly change if we are using sql developer tool to which this parameter is set to default to Byte. If we compile packages setting this parameter value to 'BYTE' error could occur.
In sql developer we can do the following to check this. Go to sql developer (Tools/Preferences/Database/NLS). Change it to ‘CHAR’ .
to change incorrect value this needs to be used:
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
(Check image1)
To fix the issue need to recompile the invalid packages after changing the parameter to 'CHAR'.
To check any other package’s compiles incorrectly run this 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;
To check Views
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;
How to Fix it?
Need to recompile the wrong packages .
Ex: ALTER PACKAGE work_order_role_api COMPILE;
+8As the error state, it’s numeric or value error. In Addition to Sahan’s answer, data type mismatches may also cause the same issue. For example, if the database type for a certain column is NUMBER and we are passing String this error may occur.
Also, I had experienced this error in the application due to introducing variables by type and length manually. Best practice would refer to them directly.
As an example, if you want to get part description to a new variable, other than using the declaration like
new_variable_for_description varchar2(200)
it would be better if you use table column reference directly
new_variable_for_description part_catalog_tab.description%type;
From this, even the column length is updated by any release, code won’t be affected.
+14I recently investigated a case where customer is getting 'Numeric or value error: character string buffer too small' when populating data in Prepare Work Order form. Error stack points to a method which returns a string of 4000 characters, which is calling inside the 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. So the code looks perfect but the user is still getting the error! 
The reason was 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.
One solution would be to use substrb() instead of substr() which will consider bytes instead of characters when splitting the string.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.