SQL ORA-06502: 字符串缓冲区太小。即使字符串大小低于声明的大小限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34720255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-06502: character string buffer too small. Even though the string size is under declared size limit
提问by Pirate X
FOR this_loop
IN (SELECT field_A, field_B
FROM TABLE_NAME
WHERE num = i_num)
LOOP
IF this_loop.field_B BETWEEN 1 AND 3
THEN
v_A := v_A || ' ' || this_loop.field_A;
ELSIF this_loop.field_B BETWEEN 4 AND 8
THEN
v_field_A := v_field_A || ' ' || this_loop.field_A; -- Error is at this line
ELSIF this_loop.field_B BETWEEN 9 AND 15
THEN
v_B := v_B || ' ' || this_loop.field_A;
END IF;
END LOOP;
Variable decalared as
变量声明为
v_field_A VARCHAR2 (100);
What I know -
我知道的 -
- Variable v_field_A cannot hold value more than 100 characters
- The output I get from the
SELECTQuery doesn't have more than 10 characters.
- 变量 v_field_A 的值不能超过 100 个字符
- 我从
SELECT查询中得到的输出不超过 10 个字符。
My question - How is even possible to face this issue of space buffer when the characters are whitin the limit of varchar2 ?
I have faced this issue few years back but last time the cause was output of selectquery. It had more than 100 characters and hence the size issue but this time it is not more than 10 characters. I'm confused. Any help is appreciated
我的问题 - 当字符在 varchar2 的限制范围内时，如何解决空间缓冲区的问题？几年前我遇到过这个问题，但上次原因是select查询的输出。它有超过 100 个字符，因此存在大小问题，但这次不超过 10 个字符。我糊涂了。任何帮助表示赞赏
回答by Lalit Kumar B
Variable v_field_A cannot hold value more than 100 characters
变量 v_field_A 的值不能超过 100 个字符
Why not? It is very much possible since you are concatenatingthe variable for each row in the CURSOR FOR LOOP.
为什么不？这是很有可能的，因为您正在为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_OUTPUTto see the current size of the variable and the new value being appended.
使用DBMS_OUTPUT查看变量的当前大小和附加的新值。
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 5to 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.
很明显，我们想将一个具有长度的字符串连接5到声明为 max size 的变量50，当前保存的值为 size 50。因此，它抛出错误ORA-06502: PL/SQL: numeric or value error: character string buffer too small。
回答by dsz
11 rows * 10 character > 100 characters => error - your concatenating many rows of 10 characters together into a one of three variables. One of them will be growing each time through the loop.
11 行 * 10 个字符 > 100 个字符 => 错误 - 您将多行 10 个字符连接在一起成为三个变量之一。其中之一将在每次循环中增长。
What am I missing?
我错过了什么？
Also beware of characters versus bytes. In a varchar2, each CHARACTER will likely take 2 bytes.
还要注意字符与字节。在 varchar2 中，每个 CHARACTER 可能会占用 2 个字节。
回答by Gordon Linoff
You can do the string concatenation in a SQL query:
您可以在 SQL 查询中进行字符串连接：
SELECT field_A,
LISTAGG(CASE WHEN field_B BETWEEN 1 AND 3 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val1,
LISTAGG(CASE WHEN field_B BETWEEN 4 AND 8 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val2,
LISTAGG(CASE WHEN field_B BETWEEN 9 AND 15 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val3
FROM TABLE_NAME
WHERE num = i_num;
This is intended to simplify your code. You are still limited by Oracle string lengths. You can get around the Oracle limit in PL/SQL using a CLOB, but that is unnecessary when the final string is only a few hundred characters.
这是为了简化您的代码。您仍然受到 Oracle 字符串长度的限制。您可以使用 CLOB 绕过 PL/SQL 中的 Oracle 限制，但是当最终字符串只有几百个字符时，这是不必要的。
回答by Ritanjan
Check how many times this condition is being executed and what values are being concatenated. Since this being concatenated multiple number of times there is a possibility that size of v_field_A is going beyond 50 characters
检查此条件执行的次数以及连接的值。由于多次连接，因此 v_field_A 的大小可能会超过 50 个字符
ELSIF this_loop.field_B BETWEEN 4 AND 8
THEN
v_field_A := v_field_A || ' ' || this_loop.field_A;
To resolve this you can increase the size of this variable. You can declare a varchar upto 32,767 bytes
要解决此问题，您可以增加此变量的大小。您可以声明最多 32,767 字节的 varchar
回答by Avrajit Roy
The answer to your question lies in how many times the loop executes and how many times it goes inside IF condition.
您的问题的答案在于循环执行了多少次以及它进入 IF 条件的次数。
EXAMPLE :
例子 ：
Condition: BETWEEN 4AND 8
条件：在4和8 之间
this_loop.field_A:= 'test';
this_loop.field_A:= '测试';
Number of times loop executes= 100
循环执行次数= 100
Due to CONCATINATIONthe size will definitely shoot up more than 100 CHAR.
由于CONCATINATION，大小肯定会超过 100 CHAR。
Similar will the case with other LOOP conditions.
其他 LOOP 条件的情况也类似。
Solution: Try using CLOBinstead of VARCHARto eliminate this issue.
解决方案：尝试使用CLOB而不是VARCHAR来消除此问题。
Oracle error are very descriptive. If it throws some error it pretty much explains the scenario :P.
Oracle 错误的描述性很强。如果它抛出一些错误，它几乎可以解释这种情况：P。
