What could be the reason for this error? If it is a column width issue, then how can we find in which column it is coming?
ORA-20001: ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
It is a column width issue. The error says you are trying to insert a 10 character value into a 5 character width column. Increase the column width.
The problem is you are trying to select/insert/update one field (data type is numeric), and you are inserting character value.
This can be a time-consuming problem to troubleshoot, because this particular Oracle error message does not indicate which column has the problem. Also, if this happens in a program or process that is loading multiple records, it doesn’t identify which record is the problem either.
One option would be to create a work table with a structure similar to the actual table, but with wider column widths. Load the problem data into this work table then check the max length of each column value.
Another thing that can cause this error is if this a direct insert based on a query over a database link, and the source database has a different characterset than the target database, and if the target database has column widths specified in bytes, not characters.
The question and ans was originally published here.