title: ORA-06502 numeric or vlaue error building string for clob
body: <p>I've spent all day looking at existing StackOverflow questions and answers on this, but so far nothing has worked.</p>
  
  <p>I think my problem is slightly different to everyone elses.</p>
  
  <p>I am building a JSON string based on a <code>SELECT</code> from the database. This needs to be able to handle 50k+ records. I am using 11g but it will be deployed on 10g as well.</p>
  
  <p>I am using a <code>CLOB</code> to store this return json string, because it has a capacity of 4GB. However, I keep getting the error: <code>ORA-06502: PL/SQL: numeric or value error</code>. Previously I was getting this error when appending a <code>varchar</code> onto my <code>clob</code>, for example <code>returnString := returnString || "some text;</code> so I believe it was casting the clob to a varchar and causing this. Since then, I've gotten rid of anywhere I think a cast could be occuring, by using <code>dbms_log.append()</code></p>
  
  <p>Here is my PL/SQL:</p>
  
  <pre><code>declare
  
    v_person_code ca_mips_queue.person_code%type;
    v_person_type ca_mips_queue.person_type%type;
  
    cursor cur_mips is
      select person_code,person_type from ca_mips_queue 
      where terminal_code = :terminal_code and (download_stage='1' or download_stage = '2');
  
    returnString clob;
    v_isFirst boolean := TRUE;
  
  begin
  
    dbms_lob.createtemporary(returnString, true);
    dbms_lob.open(returnString, DBMS_LOB.LOB_READWRITE);
    dbms_lob.append(returnString, '{"items":[');
  
    for person_rec in cur_mips
    loop
        v_person_code := person_rec.person_code;
        v_person_type := person_rec.person_type;
  
        update ca_mips_queue
        set download_stage = '2'
        where terminal_code = :terminal_code
        and person_code = v_person_code
        and person_type = v_person_type;
  
        if v_isFirst then
            dbms_lob.append(returnString, '{"person_code": "');
            v_isFirst := FALSE;
        else
            dbms_lob.append(returnString,  ',{"person_code": "');
        end if;
  
        dbms_lob.append(returnString, v_person_code);
        dbms_lob.append(returnString, '", "person_type": "');
        dbms_lob.append(returnString, v_person_type);
        dbms_lob.append(returnString, '"}');
  
        dbms_output.put_line(length(returnString));
  
    end loop;
  
    commit;
  
    dbms_lob.append(returnString, ']}');
    :result := returnString;
  
    dbms_lob.close(returnString);
  
    :status_code := 200;
  
    exception 
      when others then
        :status_code := 500;
        :exception := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(:exception);
  
  end;
  </code></pre>
  
  <p>Notice I am printing out the size of <code>returnString</code> on each loop using <code>dbms_output.put_line(length(returnString));</code>
  Here is the output (or the last few) of that:</p>
  
  <pre><code>43933
  43976
  44019
  44062
  44105
  44148
  44191
  44234
  44277
  44320
  44363
  44406
  44449
  44492
  44535
  44578
  44621
  44664
  44707
  44750
  44793
  44836
  44879
  44922
  44965
  45008
  ORA-06502: PL/SQL: numeric or value error
  </code></pre>
  
  <p>So it looks like its getting into trouble at around 45008 bytes (450kb?).</p>
  
  <p>How can this be if a <code>clob</code> has a limit of 4GB?</p>