Emailing a CLOB Document

The below code snippet is quite useful for emailing a CLOB document, which I have used for emailing a list of lines can't accommodate to a LONG object.
This can as well be used to write the output of a concurrent program to the CLOB and emailing it.

*********************************************************
DECLARE
lc_conn utl_smtp.connection; 
lc_sender             VARCHAR2(200) := 'abc@gmail.com';
lc_output_email   VARCHAR2(200) := 'xyz@gmail.com';
lc_filedata            VARCHAR2(32767);
crlf                        VARCHAR2(2)  := chr(13)||chr(10);
lc_subject             VARCHAR2(2000);
l_boundary           VARCHAR2(50) := '----=*#abc1234321cba#*=';
lc_filedata            VARCHAR2(32767);
lc_data                 CLOB := '';

BEGIN
lc_conn := utl_smtp.Open_Connection('127.0.0.1', 25);
utl_smtp.Helo(lc_conn, '127.0.0.1');
utl_smtp.Mail(lc_conn, lc_sender);
utl_smtp.rcpt(lc_conn, lc_output_email); 
utl_smtp.open_data(lc_conn);
lc_filedata := lc_filedata || 'Date:' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || crlf;
lc_filedata := lc_filedata || 'To: ' || lc_output_email || crlf;
lc_filedata := lc_filedata || 'From: ' || lc_sender || crlf;
lc_filedata := lc_filedata || 'Subject: ' || lc_subject || crlf;
lc_filedata := lc_filedata || 'MIME-Version: 1.0' ||  crlf;  
lc_filedata := lc_filedata || 'Content-Type: multipart/mixed; boundary=' ||chr(34)||l_boundary|| chr(34)||crlf;
lc_filedata := lc_filedata || '--'||l_boundary|| crlf;
lc_filedata := lc_filedata || 'Content-Type: text/plain;'|| crlf ||
                                           'Content-Transfer_Encoding: 7bit'|| crlf ||
                                           crlf || lc_body || crlf || crlf ||
                                           '--'||l_boundary|| crlf ||
                                           'Content-Type: text/plain;'|| crlf ||
                                           ' name="'||lc_filename||'"'|| crlf ||
                                           'Content-Transfer_Encoding: 8bit'|| crlf ||
                                          'Content-Disposition: attachment;'|| crlf ||
                                          ' filename="'||lc_filename||'"'|| crlf || crlf;

  -- Headers from lc_filedata
dbms_lob.createtemporary(lc_data, false, 10);
dbms_lob.write(lc_data, length(lc_filedata), 1, lc_filedata);

ln_temp := dbms_lob.getlength(lc_data) + 1;
 
-- Looping to the Table of lines to be emailed
FOR j in 1..lg_i LOOP
    ln_temp := dbms_lob.getlength(lc_data) + 1;
    IF dbms_lob.getlength(lg_varchar2(j)) > 0 THEN
       dbms_lob.write(lc_data, length(lg_varchar2(j)), ln_temp, lg_varchar2(j));
    END IF;
END LOOP;
 
ln_temp := dbms_lob.getlength(lc_data) + 1;
 
ln_temp := 1;
ln_amount := 1900;
 
WHILE ln_temp < dbms_lob.getlength(lc_data) LOOP
   utl_smtp.write_data(g_Conn, dbms_lob.substr(lc_data, ln_amount, ln_temp));
   ln_temp := ln_temp + ln_amount;
   ln_amount := least(1900, dbms_lob.getlength(lc_data) - ln_amount);
END LOOP;
 
utl_smtp.write_data(lc_Conn, crlf||crlf);
utl_smtp.close_data(lc_conn);
utl_smtp.quit(lc_conn);

*********************************************************

you can also refer to the link for different ways to email from pl/sql code.

  Print This Post