ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
The above error is thrown if a variable in pl/sql is assigned a value at runtime above its size limit. It may also occur if variables are assigned values inside an infinite loop in pl/sql block. Consider the below pl/sql procedure
RETURN VARCHAR2
AS
i VARCHAR2(1000);
v_EMP_NAMES VARCHAR2(4000);
v_cursor VARCHAR2(4000);
TYPE attr_cur IS REF cursor;
contid attr_cur;
BEGIN
v_cursor := 'select distinct emp_name from employees where dept_name = '|| dept_name;
OPEN contid FOR v_cursor;
LOOP
fetch contid INTO i;
v_EMP_NAMES := v_EMP_NAMES || TRIM(i) || ',';
END LOOP;
RETURN v_EMP_NAMES;
END;
The above function returns a comma separated list of employee names for the given department. Each employee name in the query result is concatenated(separated by commas) inside the loop. The above function will throw "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" because the loop runs infinitely as there is no exit condition. So the variable, v_SMUTR will be infinitely concatenated with employee names, beyond its size limit.
The simple way to solve this issue is to add an exit condition inside the loop as follows.
RETURN VARCHAR2
AS
i VARCHAR2(1000);
v_EMP_NAMES VARCHAR2(4000);
v_cursor VARCHAR2(4000);
TYPE attr_cur IS REF cursor;
contid attr_cur;
BEGIN
v_cursor := 'select distinct emp_name from employees where dept_name = '|| dept_name;
OPEN contid FOR v_cursor;
LOOP
fetch contid INTO i;
exit WHEN contid%notfound;
v_EMP_NAMES := v_EMP_NAMES || TRIM(i) || ',';
END LOOP;
RETURN v_EMP_NAMES;
END;