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

Question

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 any 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;

Answer 1

The easiest fix is to limit the line length to 255 characters by inserting some newlines. See this.

Answer 2

Try this DBMS_OUTPUT.ENABLE(200000);

Answer 3

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?