Why is this email getting truncated?
I use the following procedure to send emails:
procedure write_clob(p_connection in out nocopy connection, p_clob in out nocopy clob)
is
v_len integer;
v_index integer;
begin
v_len := dbms_lob.getlength(p_clob);
v_index := 1;
while v_index <= v_len loop
utl_smtp.write_data(p_connection, dbms_lob.substr(p_clob, 32000, v_index));
v_index := v_index + 32000;
end loop;
end write_clob;
Long emails seem to get truncated. I tested the post with the message v_len == 28811 which is less than 32000. However, I noticed that it does not get truncated if I change the buffer size to 3200:
procedure write_clob(p_connection in out nocopy connection, p_clob in out nocopy clob)
is
v_len integer;
v_index integer;
begin
v_len := dbms_lob.getlength(p_clob);
v_index := 1;
while v_index <= v_len loop
utl_smtp.write_data(p_connection, dbms_lob.substr(p_clob, 3200, v_index));
v_index := v_index + 3200;
end loop;
end write_clob;
I've tried with some other sizes, eg. 30,000 and 28,000, but it still gets truncated. Note that 28000 is less than the message length - 28811. Also utl_smtp_write_data
accepts varchar2, which should allow up to 32767:
UTL_SMTP.WRITE_DATA (
c IN OUT NOCOPY connection,
data IN VARCHAR2 CHARACTER SET ANY_CS);
I could just use 3200 as my buffer size, but I would rather not apply the bandid fix without understanding what is going on.
Can anyone help me understand why this strange behavior is happening? Thank!
I am using Oracle 11.2.0.3.0.
source to share
We are facing the same problem and the magic number seems to be 2,000. If you see that there are 3,200 jobs, then I am assuming that you are transferring single byte character data, with the actual limit being 4,000.
After beating our heads against this problem, did I have a "spirit"? moment (complete with indefinite question mark). I keep reading that the write_data()
writeable data limit should be 32k, but the type of the variable in the call is varchar2. While varchar2 in pl / sql should allow up to 32k, I suspect that the varchar2 database limit of 4000 bytes is being introduced inside the function utl_smtp.write_data()
- whether by some internal limitation or by explicitly truncating the incoming buffer, I don't know.
I would recommend keeping your chunk size down to 2000 or less to avoid two byte headaches on the road. In fact, every example on the Internet seems to use 1900 ... a strange number whose existence can be traced back to 13 year old code at asktom.oracle.com:
Quote:
I wish I remembered why it was in 1900 byte chunks, but that was 9 years ago. I think I should have been more specific with a comment.
Tyler
I find it more fun than I could say.
source to share