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

发布于 2024-09-14 16:17:30 字数 1773 浏览 6 评论 0原文

我有一个 oracle 函数，如下所示。当我在 sql-developer 中运行它时出现错误

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

但是，函数中的 DBMS_OUTPUT.PUT_LINE(FINAL_RESULT) 行正在输出窗口中打印预期的输出。

有人能帮我吗？

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;

原文

I have an oracle function which is given below. when I run this in sql-developer it gives an error

ORA-06502: PL/SQL: numeric or value error: character string buffer too
small error.

However the DBMS_OUTPUT.PUT_LINE(FINAL_RESULT) line in the function is printing the expected output in the output window.

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荆棘i 2024-09-21 16:17:30

向亚历克斯和托尼道歉（他们显然也有同样的想法）。

您的代码显示了预期的输出，因此 DBMS_OUTPUT 行一定已成功。

DBMS_OUTPUT.PUT_LINE(FINAL_RESULT);

QED 异常发生在此时之后。我们在函数中多了一行。

RETURN FINAL_RESULT;

有根据的猜测是，问题正如 Alex 提到的那样 - 结果对于 SQL Developer 中调用代码中定义的变量来说太大（即它不是 CLOB，而是一个对于结果来说太小的字符串）。

如果这是生成的代码，则可能是 SQL Developer 和 CLOB 函数的错误。

您是否尝试过使用 SELECT 函数 FROM DUAL 来代替？

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.PUT_LINE(FINAL_RESULT);

QED the exception is happening after this point. We have one more line in the function.

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

If this is generated code, it may be a bug with SQL Developer and CLOB functions.

Have you tried doing a SELECT function FROM DUAL instead?

回复收藏 0 原文