oracle ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3509643/
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 error
提问by Andromeda
I have an oracle function which is given below. when I run this in sql-developer it gives an error
我有一个 oracle 函数，如下所示。当我在 sql-developer 中运行它时出现错误
ORA-06502: PL/SQL: numeric or value error: character string buffer too small error.
ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小错误。
However the DBMS_OUTPUT.PUT_LINE(FINAL_RESULT)line in the function is printing the expected output in the output window.
但是，DBMS_OUTPUT.PUT_LINE(FINAL_RESULT)函数中的行正在输出窗口中打印预期的输出。
Can anyone help me in this??
任何人都可以帮助我吗？
create or replace
FUNCTION AVERAGE_WORKFORCE(PERIOD in varchar2, YR in varchar2) RETURN CLOB AS
FINAL_RESULT CLOB:=null;
STRING_QUERY_TEXT CLOB:=null;
OUTPUT_RESULT CLOB:=null;
BEGIN
FINAL_RESULT:='<FINAL_RESULT><HEADER><NAME> </NAME> <NAME>SENIOR DIRECTOR</NAME> <NAME>DIRECTOR</NAME> <NAME>EXECUTIVE</NAME> <NAME>MANAGER</NAME><NAME>CASHIER</NAME><NAME>EMPLOYEE</NAME></HEADER>';
STRING_QUERY_TEXT:='SELECT XMLElement("tuple",XMLElement("DESC",''Average number of registered employees''), XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''SENIOR DIRECTOR'')), XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''DIRECTOR'')), XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''MANAGER'')), XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''EXECUTIVE'')), XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''CASHIER'')), XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''EMPLOYEE''))) FROM DUAL';
EXECUTE_QUERY_RETURN_RESULT(STRING_QUERY_TEXT,OUTPUT_RESULT);
FINAL_RESULT:=FINAL_RESULT||''||OUTPUT_RESULT;
FINAL_RESULT:=FINAL_RESULT||''||'</FINAL_RESULT>';
DBMS_OUTPUT.PUT_LINE(FINAL_RESULT);
RETURN FINAL_RESULT;
END AVERAGE_WORKFORCE;
回答by JulesLt
With apologies to Alex and Tony (who have obviously thought the same thing).
向亚历克斯和托尼（显然他们也有同样的想法）道歉。
Your code is showing the expected output so the DBMS_OUTPUT line must have succeeded.
您的代码显示了预期的输出，因此 DBMS_OUTPUT 行必须成功。
DBMS_OUTPUT.PUT_LINE(FINAL_RESULT);
QED the exception is happening after this point. We have one more line in the function.
QED 在这一点之后发生异常。我们在函数中还有一行。
RETURN FINAL_RESULT;
An educated guess is that the problem is as Alex mentions - the result is too big for the variable defined in the calling code in SQL Developer (i.e. it is not a CLOB, but a string that is too small for the result).
有根据的猜测是，问题正如亚历克斯所提到的 - 结果对于 SQL Developer 中调用代码中定义的变量来说太大了（即它不是 CLOB，而是对于结果来说太小的字符串）。
If this is generated code, it may be a bug with SQL Developer and CLOB functions.
如果这是生成的代码，则可能是 SQL Developer 和 CLOB 函数的错误。
Have you tried doing a SELECT function FROM DUAL instead?
您是否尝试过从 DUAL 执行 SELECT 函数？
回答by baklarz2048
Try this DBMS_OUTPUT.ENABLE(200000);
试试这个 DBMS_OUTPUT.ENABLE(200000);
