title: Oracle - ORA-06502: PL/SQL: numeric or value error (DBMS_OUTPUT)
body: <p>I implemented a function that returns <strong>clob</strong> data-type, and I would like to print the result in <strong>DBMS Output</strong>. Unfortunately, I am getting <strong>ORA-06502: PL/SQL: numeric or value error</strong> and I think it is due to the size of DBMS_OUTPUT.</p>
  
  <p>This is the code.</p>
  
  <pre><code>DECLARE
  
      TYPE tp_col_array IS TABLE OF varchar2(32767); 
      FUNCTION my_fn (        
          p_in_proc_date IN varchar2)
          RETURN clob AS         
           vr_output_str clob;
  
          BEGIN            
  
          -- Detailed code hidden due to privacy. Sorry
  
          RETURN vr_output_str;
  
          EXCEPTION 
              WHEN LOGIN_DENIED
              THEN 
                  DBMS_OUTPUT.PUT_LINE('Invalid username/password: logon denied');
          RETURN 'TEST Terminated';
  
      END my_fn;
  
  BEGIN
      DBMS_OUTPUT.PUT_LINE(my_fn('31-AUG-14'));    
  END;
  </code></pre>
  
  <p>Here are something that can help you to understand this issue<br/>
  1) Added the following to set the size of buffer <strong>unlimited</strong>, but did not work..</p>
  
  <pre><code>DBMS_OUTPUT.ENABLE(NULL);
  </code></pre>
  
  <p>or</p>
  
  <pre><code>set serveroutput on size unlimited;
  </code></pre>
  
  <p>Related link: <a href="http://www.oracle-developer.net/display.php?id=327" rel="noreferrer">http://www.oracle-developer.net/display.php?id=327</a><br/><br/>
  2) It is noted that the size of vr_output_str is <strong>75387</strong>, and that is why the return type is <strong>CLOB</strong>.</p>
  
  <pre><code>DBMS_LOB.getlength(vr_output_str); // =75387
  </code></pre>
  
  <p>3) I can solve the issue by doing the following, but I think this is not a <strong>good solution</strong> since it executed the function three times. </p>
  
  <pre><code>DBMS_OUTPUT.PUT_LINE(SUBSTR(my_fn ('31-AUG-14'), 1, 32767));
  DBMS_OUTPUT.PUT_LINE(SUBSTR(my_fn ('31-AUG-14'), 32768, 32767));
  DBMS_OUTPUT.PUT_LINE(SUBSTR(my_fn ('31-AUG-14'), 65536, 32767));
  </code></pre>
  
  <p>4) I am using <strong>Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production</strong></p>