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

问题描述

我有一个下面给出的oracle函数.当我在sql-developer中运行它时，它给出了错误

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

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

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

有人可以帮我吗?

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;

推荐答案

尝试使用此DBMS_OUTPUT.ENABLE(200000);

Try this DBMS_OUTPUT.ENABLE(200000);

这篇关于ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！