Ora-06502: Pl/Sql: Numeric or Value Error: Character String Buffer Too Small

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The line in question is actually this:

gurf.xmcgurf_index := trim(substr(job_post.right_dr,1,dr_dash_loc - 1));

(You left out the gurf. part in the version you quote.)

gurf, including xmcgurf_index, is declared here:

gurf               xmcgurf_media_services%rowtype;

If you look at the definition of xmcgurf_media_services.xmcgurf_index, you will find that it is a CHAR(...) or VARCHAR2(...) or something, with a specified length; and the problem is that trim(substr(job_post.right_dr,1,dr_dash_loc - 1)) exceeds that length.

And you do in fact try to put the value of gurf.xmcgurf_index into xmcgurf_media_services.xmcgurf_index (in INSERT statements), so this is not a trivial problem: you have a database column, and a value that doesn't fit in that column, and yet you need to store the value there.

How you solve this problem is a requirements question. I see three likely possibilities:

• Maybe the database column actually needs to support wider values than it does, and you need to widen the column.
• Maybe it's not a big deal if you have to truncate the value to match the width of the column, and lose some text off the end.
• Maybe this column is optional (nullable), and it's O.K. to store null in there if the value doesn't fit.

No one here can help you determine which of these, if any, is correct for your use case.

By the way, I recommend you pay heed to the excellent advice in Ben's comments above. You may have inherited this procedure, but now it's yours, and you have to own it. If it's too big and ugly to debug it effectively, then you should strongly consider refactoring it into smaller parts that are better and easier to work with.

ORA-06502: character string buffer too small. Even though the string size is under declared size limit

Variable v_field_A cannot hold value more than 100 characters

Why not? It is very much possible since you are concatenating the variable for each row in the CURSOR FOR LOOP.

For example,

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      v_name := v_name || i.ename;
  9    END LOOP;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

Use DBMS_OUTPUT to see the current size of the variable and the new value being appended.

Let's debug

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      dbms_output.put_line('Length of new value = '||LENGTH(i.ename));
  9      v_name := v_name || i.ename;
 10      dbms_output.put_line('Length of variable = '||LENGTH(v_name));
 11    END LOOP;
 12  END;
 13  /
Length of new value = 5
Length of variable = 5
Length of new value = 5
Length of variable = 10
Length of new value = 4
Length of variable = 14
Length of new value = 5
Length of variable = 19
Length of new value = 6
Length of variable = 25
Length of new value = 5
Length of variable = 30
Length of new value = 5
Length of variable = 35
Length of new value = 5
Length of variable = 40
Length of new value = 4
Length of variable = 44
Length of new value = 6
Length of variable = 50
Length of new value = 5

Error

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9

It is pretty clear, we wanted to concatenate a string with length 5 to the variable declared as max size 50, currently holding a value of size 50. hence, it throws the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

Related Topics