Sending mail with CLOB attachment

I needed to send an e-mail with Excel file attachment. I realize that there is not enough documentation about sending mail in CLOB filetype attachment.

Here is our procedure:

CREATE OR REPLACE PROCEDURE SENDMAIL_ATT
(
 MSG_FROM     VARCHAR2,
 MSG_TO       VARCHAR2,
 MSG_SUBJECT  VARCHAR2,
 MSG_TEXT     VARCHAR2,
 MSG_ATT      CLOB,
 ATT_FILENAME VARCHAR2
) IS

 V_MAILHOST VARCHAR2(50) := 'mailhost';
 V_PORT     NUMBER(2) := 25;
 V_HELO     VARCHAR2(50) := 'localhost';

 C    UTL_TCP.CONNECTION;
 RC   INTEGER;
 CRLF VARCHAR2(2) := CHR(13) || CHR(10);
 MESG VARCHAR2(32767);

 V_BUFFER_SIZE CONSTANT PLS_INTEGER := 4000;
 V_BUFFER_NEXT PLS_INTEGER := 4000;
 V_BODY_BUFFER VARCHAR2(8000 CHAR);
BEGIN

 C := UTL_TCP.OPEN_CONNECTION(V_MAILHOST, V_PORT); -- open the SMTP port
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 RC := UTL_TCP.WRITE_LINE(C, 'HELO ' || V_HELO);
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 RC := UTL_TCP.WRITE_LINE(C, 'MAIL FROM: ' || MSG_FROM); ----- MAIL BOX SENDING THE EMAIL
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 RC := UTL_TCP.WRITE_LINE(C, 'RCPT TO: ' || MSG_TO); ----- MAIL BOX RECIEVING THE EMAIL
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 RC := UTL_TCP.WRITE_LINE(C, 'DATA'); ----- EMAIL MESSAGE BODY START
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 RC := UTL_TCP.WRITE_LINE(C,
 'Date: ' ||
 TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss'));
 RC := UTL_TCP.WRITE_LINE(C, 'From: ' || MSG_FROM);
 RC := UTL_TCP.WRITE_LINE(C, 'MIME-Version: 1.0');
 RC := UTL_TCP.WRITE_LINE(C, 'To: ' || MSG_TO);
 RC := UTL_TCP.WRITE_LINE(C, 'Subject: ' || MSG_SUBJECT);
 RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
 RC := UTL_TCP.WRITE_LINE(C, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
 RC := UTL_TCP.WRITE_LINE(C); ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
 RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
 RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/html'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE
 RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer-Encoding: 7bit');
 RC := UTL_TCP.WRITE_LINE(C);
 RC := UTL_TCP.WRITE_LINE(C, MSG_TEXT); ----- TEXT OF EMAIL MESSAGE
 RC := UTL_TCP.WRITE_LINE(C);
 RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
 RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
 RC := UTL_TCP.WRITE_LINE(C, ' name="' || ATT_FILENAME || '"');
 RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer_Encoding: 7bit'); 
 RC := UTL_TCP.WRITE_LINE(C, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
 RC := UTL_TCP.WRITE_LINE(C, ' filename="' || ATT_FILENAME || '"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
 RC := UTL_TCP.WRITE_LINE(C);

 --CLOB 
 FOR I IN 0 .. FLOOR(DBMS_LOB.GETLENGTH(MSG_ATT) / V_BUFFER_SIZE)
 LOOP
 DBMS_LOB.READ(MSG_ATT,
 V_BUFFER_NEXT,
 I * V_BUFFER_SIZE + 1,
 V_BODY_BUFFER);

 RC := UTL_TCP.WRITE_TEXT(C, V_BODY_BUFFER);

 END LOOP;

 RC := UTL_TCP.WRITE_LINE(C);
 RC := UTL_TCP.WRITE_LINE(C, '.'); ----- EMAIL MESSAGE BODY END
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 RC := UTL_TCP.WRITE_LINE(C, 'QUIT'); ----- ENDS EMAIL TRANSACTION
 DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
 UTL_TCP.CLOSE_CONNECTION(C); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
 WHEN OTHERS THEN
 RAISE;
END SENDMAIL_ATT;

In the first lines of code, you need to set these variable as your mail server info.

V_MAILHOST VARCHAR2(50) := 'mailhost';
V_PORT     NUMBER(2) := 25;
V_HELO     VARCHAR2(50) := 'localhost';

You can find anywhere above codes on the internet. The important part of the code is:

--CLOB 
 FOR I IN 0 .. FLOOR(DBMS_LOB.GETLENGTH(MSG_ATT) / V_BUFFER_SIZE)
 LOOP
 DBMS_LOB.READ(MSG_ATT,
 V_BUFFER_NEXT,
 I * V_BUFFER_SIZE + 1,
 V_BODY_BUFFER);

 RC := UTL_TCP.WRITE_TEXT(C, V_BODY_BUFFER);

 END LOOP;

In this part, we convert start to read CLOB file by buffer size, then write it in the mail with “UTL_TCP.WRITE_TEXT” procecure. We do not use “UTL_TCP.WRITE_LINE”, because this procedure writes a line character end of every buffer. So our files content is corrupted. The second important point of the procedure is setting the “Content-Transfer_Encoding” and “Content-Type” variables. You have to set these according your file type.

RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/plain;'); ----- My Excel file is in plain text format
RC := UTL_TCP.WRITE_LINE(C, ' name="' || ATT_FILENAME || '"');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer_Encoding: 7bit');

Then, our procedure is ready to compile. Here is an example of use this procedure.

DECLARE
 ASDAS CLOB := 'asdşasldkaşskdj şaksjd kasjd kasjşd kasşdk jaşsk şsdk jaşld jşalskd jşaskd jşalskd jşal jk';
BEGIN
 SENDMAIL_ATT(MSG_FROM     => 'asdasd@bbb.com.tr',
 MSG_TO       => 'ozay.akdora@bbb.com.tr',
 MSG_SUBJECT  => 'Mail test subject',
 MSG_TEXT     => 'Mail text body',
 MSG_ATT      => ASDAS,
 ATT_FILENAME => 'ozay.txt');
END;

If you want to try it with an Excel file, check my prev post to create an Excel file in CLOB type.

Have nice coding

Reference : http://www.orafaq.com/wiki/Send_mail_from_PL/SQL