Akdora’s Blog

Programming, Oracle, Life, Fun

Sending mail with CLOB attachment August 13, 2009

Filed under: Oracle,PL/SQL — Akdora @ 6:15 am
Tags: , , , , , , , , , ,

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


About these ads
 

7 Responses to “Sending mail with CLOB attachment”

  1. [...] Ozay Akdora – Sending mail with CLOB attachement [...]

  2. hotspot Says:

    nice example though i am having quite a strange error while executing it with more than 1 recepient in the MSG_TO list..

    it shows me ORA-29260: network error: TNS:packet writer failure
    but it does not relate to network problems – it definitely relates to the pl/sql part

  3. rhumbert Says:

    Good code, but you should allow sending mail to more than one recipient, code example:
    v_recipient_email_addresses: = recipient_email_addresses;
    v_recipient_email_length: = length (v_recipient_email_addresses)
    v_recipient_email_addresses: = v_recipient_email_addresses ||’,’; – Add comma for the last asddress
    v_next_column: = 1;
    if instr (v_recipient_email_addresses, ‘,’) = 0 then
    – Single E-mail address
    v_single_recipient_email_addr: = v_recipient_email_addresses;
    v_recipient_email_length: = 1;
    end if;

  4. Hi,
    this program is working fine. But I want to send French characters in my attachments. When I tried to send french letters in a attchment , i am not getting appropriate french letter int he attached text file. let me know of to fix this multi character letters.
    thanks,
    Chidam Alagar

  5. Paul Randall Says:

    The FOR-LOOP uses FLOOR to round down to an integer. So if your CLOB message is exactly 4000 in size you will enter the loop a second time and get a NO DATA FOUND error trying to read the CLOB at position 4001. Oops. Maybe use a counter variable instead with WHILE and do not use FLOOR?

    WHILE V_COUNT_LOOPS < DBMS_LOB.GETLENGTH(env) / V_BUFFER_SIZE

  6. vvek Says:

    i am getting following error:
    ORA-06502: PL/SQL: numeric or value error


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 25 other followers