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

2021-12-24 00:00:00 while-loop sql if-statement oracle plsql

我尝试了以下代码的不同方式，例如取出 while 或 if，但是当我将两者放在一起(if 和 while)时，我总是在最后得到错误...

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...

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;
/

错误:

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"

通过将我声明变量a"的方式更改为:

a varchar2(2000);

*注意，这里的重大变化是使用 VARCHAR2 而不是 CHAR(不是更大的长度).根据@user272735 的回答，这就是关键.

*Notice that here, the significant change is to use VARCHAR2 instead of CHAR (not the bigger length). According to @user272735 's answer, that's the key.

推荐答案

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

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

是因为您将字符串声明为固定长度(比如 20)，并且在代码中的某个点为它分配了一个长度超过您声明的值的值.

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.

例如:

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

会触发这样的错误

相关文章