DB: Oracle 11gR2
client machine: Windows 7
Hello,
I am trying to email a HTML message body (bunch of tables, total size ~ 100 KB) using UTL_SMTP. Because it's more than 32 KB, I used the message body as CLOB type, then using UTL_SMTP.write_data inside a loop
proc1:
-- SEND THE EMAIL IN 1900 BYTE CHUNKS TO UTL_SMTP
l_offset := 1;
l_amount := 1900;
UTL_SMTP.open_data (l_connection);
WHILE l_offset < DBMS_LOB.getlength (l_body_html)
LOOP
UTL_SMTP.write_data (l_connection,
DBMS_LOB.SUBSTR (l_body_html, l_amount, l_offset));
l_offset := l_offset + l_amount;
l_amount := LEAST (1900, DBMS_LOB.getlength (l_body_html) - l_amount);
END LOOP;
UTL_SMTP.close_data (l_connection);
UTL_SMTP.quit (l_connection);
DBMS_LOB.freetemporary (l_body_html);
Calling program Proc2 structure:
proc2:
HTML body ...;
Call Proc 1(email_to, email_from, message_body);
It works when there is not much data, but throws the following error as soon as goes beyond certain size:
begin
prc2;
end;
Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "proc1", line ...
ORA-06512: at line 2
Script Terminated on line 3.
My understanding was that, inside the loop there should be virtually no limit of how big the message body can be. Can any please shed some light on why it's happening. Could it be an issue on the setup of mail server (though the error is an Oracle error)?
Thanks