title: PL/SQL DBMS_XMLQUERY max size
body: <p>My oracle version is 11g release 2</p>
  
  <p>Our system is using DBMS_XMLQUERY to transform sql result into xml, but recently the data is becoming large and we get this error: <code>ORA-06502: PL/SQL: numeric or value error</code></p>
  
  <p>The reason seems to be DBMS_XMLQUERY cannot handle too many records, but <a href="http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_xmlque.htm" rel="nofollow">oracle's official document</a> doesn't show the limitation. So maybe I have done something wrong. You can reproduce the problem in the following steps:</p>
  
  <p>step1:</p>
  
  <blockquote>
    <p>create table XMLDATA (    data_id int primary key,    data_str
    VARCHAR2(100) );</p>
  </blockquote>
  
  <p>step2:</p>
  
  <pre><code>INSERT INTO XMLDATA values(1, 'test0123456789');
  INSERT INTO XMLDATA values(2, 'test0123456789');
  INSERT INTO XMLDATA values(3, 'test0123456789');
  ....
  INSERT INTO XMLDATA values(500, 'test0123456789');
  </code></pre>
  
  <p>step3:</p>
  
  <pre><code>CREATE OR REPLACE 
  function test(total in int) return clob is
  i int;
  vn_ctx              DBMS_XMLQUERY.ctxHandle;
  BEGIN
      vn_ctx := DBMS_XMLQUERY.NEWCONTEXT('select data_id, data_str from XMLDATA where rownum &lt;= ' || total);
      DBMS_XMLQuery.propagateOriginalException(vn_ctx,true);
      DBMS_XMLQUERY.useNullAttributeIndicator(vn_ctx,true); 
      DBMS_XMLQUERY.SETROWTAG(vn_ctx, 'ITEM');
      DBMS_XMLQUERY.SETROWSETTAG(vn_ctx, 'PODATA');
      return DBMS_XMLQUERY.GETXML(vn_ctx);
  END;
  </code></pre>
  
  <p>step4:</p>
  
  <p>execute function test with a number greater than 400. Then you'll get 'ORA-06502: PL/SQL: numeric or value error' </p>
  
  <p>thanks in advance</p>
  
  <p><strong>EDIT</strong></p>
  
  <p><strong>Really sorry... I got the old code, someone adds a log statement without noticing me. The log statement can only accept a maximum of 32767 characters in one line, so the error is raised. The above function is executed by a debug tool, which gives the same error, so it's the tool's problem, not oracle.</strong></p>
  
  <p><strong>Thanks for your answering and sorry for my naive mistakes...</strong></p>