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

1
votes

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;
3
oracleplsql
How are you calling the function? – Alex Poole
i am running this function directly from the sql-developer.. – Andromeda
What is the specification of procedure EXECUTE_QUERY_RETURN_RESULT? – Tony Andrews
I meant, can you post the call you're making to the function - is it a straight select, or an assignment in an anonymous block? It looks like maybe you're doing something like declare x varchar2(255); begin x:= average_workforce(<period>,<year>); end;/ which will give that error if the varchar is smaller than the result. But need to see what you're actually doing. It appears the function is OK so it must be the caller that's complaining? – Alex Poole

3 Answers

1
votes

Try this DBMS_OUTPUT.ENABLE(200000);

6
votes

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
votes

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