ORA-06502 numeric or vlaue error building string for clob

Question

I've spent all day looking at existing StackOverflow questions and answers on this, but so far nothing has worked.

I think my problem is slightly different to everyone elses.

I am building a JSON string based on a SELECT 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.

I am using a CLOB to store this return json string, because it has a capacity of 4GB. However, I keep getting the error: ORA-06502: PL/SQL: numeric or value error. Previously I was getting this error when appending a varchar onto my clob, for example returnString := returnString || "some text; 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 dbms_log.append()

Here is my PL/SQL:

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;

Notice I am printing out the size of returnString on each loop using dbms_output.put_line(length(returnString)); Here is the output (or the last few) of that:

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

So it looks like its getting into trouble at around 45008 bytes (450kb?).

How can this be if a clob has a limit of 4GB?

Answer 1

I ran the code by doing some slight changes and it seems to be working beyond the 450kb.

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;

  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;

      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;

end;

Here is the output for the last few rows.

146471
146545
146639
146726
146803
146880
146958
147036
147116
147194
147267
147350
147419
147489
147559
147630
147706
147778
147850
147923
147995
148068
148135
148203
148278
148360
148437
148510
148585
148666
148746
148821
148899
148985
149060
149137
149211
149305

Statement processed.

Please note that I have commented the following line.

:result := returnString;

So I would say that the problem does not reside in the loop as Alex Poole has already pointed out.

Can you please try these changes in your code.