title: ORA-06502: PL/SQL: numeric or value error: character string buffer too small error
body: <p>I have an oracle function which is given below. when I run this in sql-developer it gives an error </p>
  
  <blockquote>
    <p>ORA-06502: PL/SQL: numeric or value error: character string buffer too
    small error.</p>
  </blockquote>
  
  <p>However the <code>DBMS_OUTPUT.PUT_LINE(FINAL_RESULT)</code> line in the function is printing the expected output in the output window. </p>
  
  <p>Can anyone help me in this??</p>
  
  <pre><code>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:='&lt;FINAL_RESULT&gt;&lt;HEADER&gt;&lt;NAME&gt; &lt;/NAME&gt; &lt;NAME&gt;SENIOR DIRECTOR&lt;/NAME&gt; &lt;NAME&gt;DIRECTOR&lt;/NAME&gt; &lt;NAME&gt;EXECUTIVE&lt;/NAME&gt; &lt;NAME&gt;MANAGER&lt;/NAME&gt;&lt;NAME&gt;CASHIER&lt;/NAME&gt;&lt;NAME&gt;EMPLOYEE&lt;/NAME&gt;&lt;/HEADER&gt;';    
      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||''||'&lt;/FINAL_RESULT&gt;';
      DBMS_OUTPUT.PUT_LINE(FINAL_RESULT);
      RETURN FINAL_RESULT;
  END AVERAGE_WORKFORCE;
  </code></pre>