ORA-06502: PL/SQL: 数值或值错误: Oracle 聚合函数的字符串缓冲区太小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3005244/
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: PL/SQL: numeric or value error: character string buffer too small with Oracle aggregate functions
提问by Tunde
Good day gurus,
大师们好，
I have a script that populates tables on a regular basis that crashed and gave the above error. The strange thing is that it has been running for close to 3 months on the production system with no problems and suddenly crashed last week. There has not been any changes on the tables as far as I know.
我有一个定期填充表格的脚本，该脚本崩溃并出现上述错误。奇怪的是，它在生产系统上运行了近3个月没有任何问题，上周突然崩溃。据我所知，表上没有任何变化。
Has anyone encountered something like this before? I believe it has something to do with the aggregate functions I'm implementing in it; but it worked initially.
有没有人遇到过这样的事情？我相信这与我在其中实现的聚合函数有关；但它最初有效。
please; kindly find attached the part of the script I've developed into a procedure that I reckon gives the error.
请; 请附上我开发的脚本部分，我认为该程序会导致错误。
CREATE OR REPLACE PROCEDURE V1 IS
创建或替换过程 V1 是
--DECLARE
- 宣布
v_a VARCHAR2(4000);
v_b VARCHAR2(4000);
v_c VARCHAR2(4000);
v_d VARCHAR2(4000);
v_e VARCHAR2(4000);
v_f VARCHAR2(4000);
v_g VARCHAR2(4000);
v_h VARCHAR2(4000);
v_i VARCHAR2(4000);
v_j VARCHAR2(4000);
v_k VARCHAR2(4000);
v_l VARCHAR2(4000);
v_m VARCHAR2(4000);
v_n NUMBER(10);
v_o VARCHAR2(4000);
--
-- Procedure that populates DEMO table
--
-- 填充DEMO表的过程
BEGIN
-- Delete all from the DEMO table
DELETE FROM DEMO;
-- Populate fields in DEMO from DEMOV1
INSERT INTO DEMO(ID, D_ID, CTR_ID, C_ID, DT_NAM, TP, BYR, ENY,
ONG, SUMM, DTW, REV, LD, MD, STAT, CRD)
SELECT ID, D_ID, CTR_ID, C_ID, DT_NAM, TP, TO_NUMBER(TO_CHAR(BYR,'YYYY')),
TO_NUMBER(TO_CHAR(NVL(ENY,SYSDATE),'YYYY')), CASE WHEN ENY IS NULL THEN 'Y' ELSE 'N' END, SUMMARY, DTW,
REV, LD, MD, '1', SYSDATE
FROM DEMOV1;
-- LOOP THROUGH DEMO TABLE
FOR j IN (SELECT ID, CTR_ID, C_ID FROM DEMO)
LOOP
Select semic_concat(TXTDESC)
INTO v_a
From GEOT
WHERE ID = j.ID;
SELECT COUNT(*)
INTO v_n
FROM MERP M, PROJ P
WHERE M.MID = P.COD
AND ID = j.ID
AND PROAC IS NULL;
IF (v_n > 0)
THEN
Select semic_concat(PRO)
INTO v_b
FROM MERP M, PROJ P
WHERE M.MID = P.COD
AND ID = j.ID;
ELSE
Select semic_concat(PRO || '(' || PROAC || ')' )
INTO v_b
FROM MERP M, PROJ P
WHERE M.MID = P.COD
AND ID = j.ID;
END IF;
Select semic_concat(VOCNAME('P02',COD))
INTO v_c
From PAR
WHERE ID = j.ID;
Select semic_concat(VOCNAME('L05',COD))
INTO v_d
From INST
WHERE ID = j.ID;
Select semic_concat(NVL(AUTHOR,'Anon') ||' ('||to_char(PUB,'YYYY')||') '||TITLE||', '||EDT)
INTO v_e
From REFE
WHERE ID = j.ID;
Select semic_concat(NAM)
INTO v_f
FROM EDM E, EDO EO
WHERE E.EDMID = EO.EDOID
AND ID = j.ID;
Select semic_concat(VOCNAME('L08', COD))
INTO v_g
FROM AVA
WHERE ID = j.ID;
SELECT or_concat(NAM)
INTO v_o
FROM CON
WHERE ID = j.ID
AND NAM = 'Unknown';
IF (v_o = 'Unknown')
THEN
Select or_concat(JOBTITLE || ' (' || EMAIL || ')')
INTO v_h
FROM CON
WHERE ID = j.ID;
ELSE
Select or_concat(NAM || ' (' || EMAIL || ')')
INTO v_h
FROM CON
WHERE ID = j.ID;
END IF;
Select commaencap_concat(COD)
INTO v_i
FROM PAR
WHERE ID = j.ID;
IF (v_i = ',')
THEN
v_i := null;
ELSE
Select commaencap_concat(COD)
INTO v_i
FROM PAR
WHERE ID = j.ID;
END IF;
Select commaencap_concat(COD)
INTO v_j
FROM INST
WHERE ID = j.ID;
IF (v_j = ',')
THEN
v_j := null;
ELSE
Select commaencap_concat(COD)
INTO v_j
FROM INST
WHERE ID = j.ID;
END IF;
Select commaencap_concat(COD)
INTO v_k
FROM SAR
WHERE ID = j.ID;
IF (v_k = ',')
THEN
v_k := null;
ELSE
Select commaencap_concat(COD)
INTO v_k
FROM SAR
WHERE ID = j.ID;
END IF;
Select commaencap_concat(CONID)
INTO v_l
FROM CON
WHERE ID = j.ID;
IF (v_l = ',')
THEN
v_l := null;
ELSE
Select commaencap_concat(CONID)
INTO v_l
FROM CON
WHERE ID = j.ID;
END IF;
Select commaencap_concat(PROID)
INTO v_m
FROM PRO
WHERE ID = j.ID;
IF (v_m = ',')
THEN
v_m := null;
ELSE
Select commaencap_concat(PROID)
INTO v_m
FROM PRO
WHERE ID = j.ID;
END IF;
-- UPDATE DEMO TABLE
UPDATE DEMO
SET GEOC = v_a,
PRO = v_b,
PAR = v_c,
INS = v_d,
REFER = v_e,
ORGR = v_f,
AVAY = v_g,
CON = v_h,
DTH = v_i,
INST = v_j,
SA = v_k,
CC = v_l,
EDPR = v_m,
CTR = (SELECT NAM
FROM EDM
WHERE EDMID = j.CTR_ID),
COLL = (SELECT NAM
FROM EDM
WHERE EDMID = j.C_ID)
WHERE ID = j.ID;
END LOOP;
END V1;
/
/
The aggregate functions, commaencap_concat (encapsulates with a comma), or_concat (concats with an or) and semic_concat(concats with a semi-colon).
聚合函数，commaencap_concat（用逗号封装）、or_concat（用或连接）和 semic_concat（用分号连接）。
the remaining tables used are all linked to the main table DEMO.
其余使用的表都链接到主表DEMO。
I have checked the column sizes and there seems to be no problem. I tried executing the SELECT statements alone and they give the same error without populating the tables.
我检查了列大小，似乎没有问题。我尝试单独执行 SELECT 语句，但它们在不填充表的情况下给出了相同的错误。
Any clues?
有什么线索吗？
Many thanks for your anticipated support.
非常感谢您的预期支持。
Thanks APC for all your help; particularly the rowtype and debug advice. I have checked all columns against each other and have made those that were less than 4000 bytes to 4000 bytes in size but I'm still getting the same error message.
感谢 APC 的所有帮助；特别是行类型和调试建议。我已经相互检查了所有列，并将那些小于 4000 字节的列设置为 4000 字节，但我仍然收到相同的错误消息。
The SELECT statement I tried was with one of the aggregate functions that is in the procedure:
我尝试的 SELECT 语句与过程中的聚合函数之一：
Select semic_concat(TXTDESC)
-- INTO v_a
From GEOT
WHERE ID IN (SELECT ID FROM DEMO);
and it raised the same error of string buffer too small; but when I added a group by to the statement it worked.
并且它引发了字符串缓冲区太小的相同错误；但是当我在语句中添加一个组时它起作用了。
Select semic_concat(TXTDESC)
-- INTO v_a
From GEOT
WHERE ID IN (SELECT ID FROM DEMO)
GROUP BY ID;
I tried swapping all occurrences of such statements in the procedure but it still gives the same error. Thanks once again; still working on it.
我尝试在过程中交换所有出现的此类语句，但它仍然给出相同的错误。再次感谢; 仍在努力。
回答by APC
Aggregating functions, er, aggregate. That means that they add numbers or concatenate strings together. The larger the source values the bigger the aggegated product.
聚合函数，呃，聚合。这意味着它们将数字相加或将字符串连接在一起。源值越大，聚合产品就越大。
Without knowing more details it is hard to be certain, but the most likely explanation is that something in your datahas changed, with the consequence that your aggregated value is now too big to fit in the target column.
在不了解更多细节的情况下很难确定，但最可能的解释是您的数据中的某些内容发生了变化，结果您的聚合值现在太大而无法放入目标列。
edit
编辑
" I tried the SELECT statement on its own and gives the same error... "
“我自己尝试了 SELECT 语句并给出了同样的错误......”
Hmmm, which SELECT? Your procedure has got oodles of them.
嗯，哪个选择？你的程序有很多。
Anyway, the thing you need to do is learn to debug your code. (1)
不管怎样，你需要做的就是学会调试你的代码。(1)
The best way to debug PL/SQL is with an IDE which supports such endeavours. Both TOAD and PL/SQL Developer do so, as does Oracle's own (free) product SQL Developer. Find out more.
调试 PL/SQL 的最佳方法是使用支持此类努力的 IDE。TOAD 和 PL/SQL Developer 都这样做，Oracle 自己的（免费）产品 SQL Developer 也是如此。 了解更多。
Alternatively you can use DBMS_OUTPUT (AKA the Devil's Debugger) and interpolate lots of DBMS_OUTPUT.PUT_LINE()calls to see which statement you're about to execute, and the length of the relevant values.
或者，您可以使用 DBMS_OUTPUT（又名魔鬼调试器）并插入大量DBMS_OUTPUT.PUT_LINE()调用以查看您将要执行的语句以及相关值的长度。
However, given that all your PL/SQL string variables are the maximum SQL column length - varchar2(4000)- I would focus on the code which populates your v_nvariable. number(10)is by no means the biggest number it is possible to hold in SQL, so as you are breaching a buffer limit that would seem the most likely candidate. Although, since the error message does mention stringbuffer I may be selling you a bum steer.
但是，鉴于您所有的 PL/SQL 字符串变量都是最大 SQL 列长度varchar2(4000)- 我将重点关注填充您的v_n变量的代码。 number(10)绝不是 SQL 中可以容纳的最大数字，因此您违反了看起来最有可能的缓冲区限制。虽然，由于错误消息确实提到了字符串缓冲区，我可能会卖给你一个流浪汉。
The other possibility is that final UPDATE statement. Are all the columns of DEMO sized as varchar2(4000)? If not then you need to look at them. It is good practice to specify variables using the %TYPE syntax:
另一种可能性是最后的 UPDATE 语句。DEMO 的所有列的大小是否为varchar2(4000)? 如果没有，那么您需要查看它们。使用 %TYPE 语法指定变量是一种很好的做法：
v_a demo.geoc%TYPE;
v_b demo.pro%TYPE;
Or, to save typing, specify a single record variable:
或者，为了节省输入，指定一个记录变量：
v_demo demo%rowtype;
Which you can reference like this:
你可以这样参考：
select semic_concat(TXTDESC)
into v_demo.geoc
From GEOT
WHERE ID = j.ID;
(Incidentally it possible to use a row level variable in update statements using the UPDATE ... SET ROW =syntax, but I don't think that would be appropriate in your situation.)
（顺便说一句，可以使用UPDATE ... SET ROW =语法在更新语句中使用行级变量，但我认为这不适合您的情况。）
edit 2
编辑 2
Again, NO_DATA_FOUNDpoints to a data issue. Unless our database is read only, we must expect that data will change and we should handle data-related exceptions. If the reason you don't handle NO_DATA_FOUNDis because the data should always be there you have a broader problem, possibly a missing or disabled foreign key. Generally it is safer to assume that we will get NO_DATA_FOUND, TOO_MANY_ROWS, etc and include helpful exception handlers to log the relevant details.
再次NO_DATA_FOUND指出数据问题。除非我们的数据库是只读的，否则我们必须预料到数据会发生变化，我们应该处理与数据相关的异常。如果您不处理的原因NO_DATA_FOUND是因为数据应该始终存在，那么您会遇到更广泛的问题，可能是丢失或禁用了外键。一般是比较安全的假设，我们会得到NO_DATA_FOUND，TOO_MANY_ROWS等，并包括有帮助的异常处理程序来记录相关细节。
footnote (1)Or learn to develop it Test First using a unit test harness, but this is a stable door, horse gone scenario...
脚注 (1)或者学习开发它 Test First 使用单元测试工具，但这是一个稳定的门，马走了的场景......
