Issue in appending the LOBs -ORA-06502: PL/SQL: numeric or value error
Hi
I am using Oracle 11g.
I have a requirement in which I have to append LOBs and I have to insert the LOB into a table column. I am facing problem when the data exceeds certain limit. My program works like the below:
(please note that my program logic is given below, not the exact program)
DECLARE
final_html CLOB;
int_html CLOB;
v_str VARCHAR2(32767);
i number:=0;
BEGIN
dbms_lob.createtemporary(lob_loc => int_html, CACHE => TRUE, dur => dbms_lob.CALL);
dbms_lob.createtemporary(lob_loc => final_html, CACHE => TRUE, dur => dbms_lob.CALL);
DBMS_LOB.OPEN(int_html, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(final_html, DBMS_LOB.LOB_READWRITE);
dbms_output.ENABLE(1000000);
FOR i IN 1..100 loop
v_str := '<tr>' ||
'<td style="WORD-WRAP: break-word" width="50"><font size="2">' ||
i || '</font></td>' ||
'</tr>';
dbms_lob.writeappend(lob_loc => int_html, amount => LENGTH(v_str), BUFFER => v_str);
END LOOP;
dbms_output.put_line( 'The length of int_html is:'||dbms_lob.getlength(int_html));
dbms_output.put_line( 'The int_html is:'||int_html);
final_html:= '<html>'||int_html||'</html>';
dbms_output.put_line('The final_html is:'||final_html);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||'-'||SQLERRM);
DBMS_LOB.CLOSE (int_html);
DBMS_LOB.CLOSE (final_html);
DBMS_LOB.FREETEMPORARY(int_html);
DBMS_LOB.FREETEMPORARY(final_html);
END;
when the looping is done for lesser value say for e.g FOR i IN 1..10 loop, the program works fine, but when the looping is done for more values I am getting "-6502-ORA-06502: PL/SQL: numeric or value error" error message.
Please help me to resolve this issue.
Thanking you in advance.
Regards,
Sri