ORA-06502：字符串缓冲区太小。即使字符串大小低于声明的大小限制

例如，

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

使用DBMS_OUTPUT可以查看变量的当前大小以及附加的新值。

让我们调试

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

错误

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

很明显，我们想将一个长度5为字符串的字符串连接到声明为max size的变量上，该变量50当前具有size的值50。因此，它将引发错误ORA-06502: PL/SQL: numeric or value error: character string buffer too small。