ORA-06502：PL / SQL：数字或值错误：字符串缓冲区太小

时间：2022-12-20 20:22:18

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end...

我尝试了以下代码的不同方式，比如取出while或if，但是当我把两者放在一起时（if和while），我总是得到错误...

undefine numero
set serveroutput on
accept numero prompt 'Type # between 100 and 999: '
declare
   i number:=1;
   a char(25);
   b char(1);
   c varchar2(10);
   d number;
begin
   c := №
   d := length(c);
   b := substr(c, i, 1);
   while i <= d loop
     if b = '1' then
       a:= a||'one ';
     end if;
     i := i+1;
   end loop;
   dbms_output.put_line('The number is '||a);
end;
/

ERROR:

错误：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
06502. 00000 -  "PL/SQL: numeric or value error%s"

FIXED by changing how I declared the variable "a" to:

通过更改我将变量“a”声明为：

a varchar2(2000);

2 个解决方案

#1

24  

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

PL / SQL：数字或值错误：字符串缓冲区太小

is due to the fact that you declare a string to be of a fixed length (say 20), and at some point in your code you assign it a value whose length exceeds what you declared.

是因为你声明一个字符串是一个固定的长度（比如说20），并且在你的代码中的某个时刻你给它赋一个长度超过你声明的值的值。

for example:

例如：

myString VARCHAR2(20);
myString :='abcdefghijklmnopqrstuvwxyz'; --length 26

will fire such an error

会发生这样的错误

#2

9  

CHAR is a fixed-length data type that uses as much space as possible. So a:= a||'one '; will require more space than is available. Your problem can be reduced to the following example:

CHAR是固定长度的数据类型，使用尽可能多的空间。所以a：= a ||'one';将需要更多的空间。您的问题可以简化为以下示例：

declare
  v_foo char(50);
begin
  v_foo := 'A';
  dbms_output.put_line('length of v_foo(A) = ' || length(v_foo));
  -- next line will raise:
  -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  v_foo := v_foo || 'B';
  dbms_output.put_line('length of v_foo(AB) = ' || length(v_foo));  
end;
/

Never use char. For rationale check the following question (read also the links):

永远不要使用char。对于理由，请检查以下问题（另请阅读链接）：

#1

24  

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

PL / SQL：数字或值错误：字符串缓冲区太小

is due to the fact that you declare a string to be of a fixed length (say 20), and at some point in your code you assign it a value whose length exceeds what you declared.

是因为你声明一个字符串是一个固定的长度（比如说20），并且在你的代码中的某个时刻你给它赋一个长度超过你声明的值的值。

for example:

例如：

myString VARCHAR2(20);
myString :='abcdefghijklmnopqrstuvwxyz'; --length 26

will fire such an error

会发生这样的错误

#2

9  

CHAR is a fixed-length data type that uses as much space as possible. So a:= a||'one '; will require more space than is available. Your problem can be reduced to the following example:

CHAR是固定长度的数据类型，使用尽可能多的空间。所以a：= a ||'one';将需要更多的空间。您的问题可以简化为以下示例：

declare
  v_foo char(50);
begin
  v_foo := 'A';
  dbms_output.put_line('length of v_foo(A) = ' || length(v_foo));
  -- next line will raise:
  -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  v_foo := v_foo || 'B';
  dbms_output.put_line('length of v_foo(AB) = ' || length(v_foo));  
end;
/

Never use char. For rationale check the following question (read also the links):

永远不要使用char。对于理由，请检查以下问题（另请阅读链接）：

标签：

相关文章