CODEWITHSUNDEEP
oracle-databaseplsqlsmtpattachment
May12
May12

Reputation: 2520

UTL_SMTP: procedure for sending html+css table in message body and attachement in single letter

Could anybody help me to correct code to send mail with attachment. As result I should send a mail with small table (prepared using html and css) in letter body and .csv as an attachment. The procedure works fine only with message body, but it does not want to send attachment =( : 

CREATE OR REPLACE PROCEDURE test_send_mail(
    p_sender    IN VARCHAR2,
    p_recipient IN VARCHAR2,
    p_subject   IN VARCHAR2,
    p_message   IN CLOB – table in html)
as
    l_mailhost VARCHAR2(255) := 'localhost';
    l_mail_conn utl_smtp.connection;

    v_add_src  VARCHAR2(4000);
    v_addr     VARCHAR2(4000);
    slen number := 1;

    crlf VARCHAR2(2)  := chr(13)||chr(10);

    i      NUMBER(12);
    len   NUMBER (12);
    part  NUMBER(12) := 16384;

    -- css for table in letter body
    l_stylesheet CLOB := '
       <html><head>
       <style type="text/css">
                   body     { font-family     : Verdana, Arial;
                              font-size       : 10pt;}

                   .green   { color           : #00AA00;
                              font-weight     : bold;}

                   .red     { color           : #FF0000;
                              font-weight     : bold;}

                   pre      { margin-left     : 10px;}

                   table    { empty-cells     : show;
                              border-collapse : collapse;
                              width           : 100%;
                              border          : solid 2px #444444;}

                   td       { border          : solid 1px #444444;
                             font-size       : 12pt;
                              padding         : 2px;}

                   th       { background      : #EEEEEE;
                              border          : solid 1px #444444;
                              font-size       : 12pt;
                              padding         : 2px;}

                   dt       { font-weight     : bold; }

                  </style>
                 </head>
                 <body>';


-- variables for attachment
/*Table header in attachment*/
lv_mycolumns   VARCHAR2(32000):= 'A1'||chr(9)||'A2'||chr(9)||'A3'||chr(13)||chr(10);               
lv_message     VARCHAR2(32000);

CURSOR cur_query
IS
SELECT 'help' as  A1, 'me' as  A3, 'please' as  A3
from DUAL; -- I would like to see this table in the attachment

attached_file_name VARCHAR2(50):= 'REPORT_';
ddate VARCHAR2(16):= TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');
filetype varchar(10):= '.xls'; -- really csv  

BEGIN
   l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
    utl_smtp.helo(l_mail_conn, l_mailhost);
    utl_smtp.mail(l_mail_conn, p_sender);
    if(instr(p_recipient,';') = 0) then
        utl_smtp.rcpt(l_mail_conn, p_recipient);
    else
        v_add_src := p_recipient || ';';
        while(instr(v_add_src,';',slen) > 0) loop
            v_addr := substr(v_add_src, slen, instr(substr(v_add_src, slen),';')-1);
            slen := slen+instr(substr(v_add_src, slen),';');
            Dbms_Output.put_line('rcpt ' || v_addr);
            utl_smtp.rcpt(l_mail_conn, v_addr);
    end loop;

  end if;

    utl_smtp.open_data(l_mail_conn );
    utl_smtp.write_data(l_mail_conn,
     'MIME-version: 1.0' || crlf ||
     'Content-Type: text/html; charset=ISO-8859-15' || crlf ||
     'Content-Transfer-Encoding: 8bit' || crlf ||
     'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
     'From: '   || p_sender || crlf ||
     'Subject: '|| p_subject || crlf ||
     'To: '     || p_recipient || crlf);
    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(l_stylesheet));

    i   := 1;
    len := DBMS_LOB.getLength(p_message);
    WHILE (i < len) LOOP
        utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(DBMS_LOB.SubStr(p_message,part, i)));
        i := i + part;
    END LOOP;
    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('</body></html>'));


-- begin the attachment. This part I need to correct. How?


FOR rec IN cur_query
   LOOP
      lv_message := rec.A1||chr(9)||rec.A2||chr(9)||rec.A3||chr(13)||chr(10);
   END LOOP;


  -- end connectin
    utl_smtp.close_data(l_mail_conn );
    utl_smtp.quit(l_mail_conn);
end;

Thank you in advance.

Upvotes: 0

Views: 9792

Answers (1)

DazzaL
DazzaL

Reputation: 21973

you are not setting the correct content type, nor are you defining boundaries correctly (read up on Content-Type: multipart/mixed).

eg (also remember, if you want to attach binary, you have to base64 encode it):

the boundary "---YOURBOUNDARY" should not occur in the email body at all , so make sure you pick something that would never appear :)

    utl_smtp.open_data(l_mail_conn );
    utl_smtp.write_data(l_mail_conn,
     'MIME-version: 1.0' || crlf ||
     'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
     'From: '   || p_sender || crlf ||
     'Subject: '|| p_subject || crlf ||
     'To: '     || p_recipient || crlf || 
     'Content-Type: multipart/mixed;' || crlf ||
     ' boundary="---YOURBOUNDARY"' || crlf ||crlf);

    utl_smtp.write_data(l_mail_conn, '-----YOURBOUNDARY'||crlf);
    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html' || crlf);
    utl_smtp.write_data(l_mail_conn,  'Content-Transfer-Encoding: 8bit' || crlf || crlf);
    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(l_stylesheet));

    i   := 1;
    len := DBMS_LOB.getLength(p_message);
    WHILE (i < len) LOOP
        utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(DBMS_LOB.SubStr(p_message,part, i)));
        i := i + part;
    END LOOP;
    utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('</body></html>'));
    utl_smtp.write_data(l_mail_conn,  crlf || crlf);

-- begin the attachment. This part I need to correct. How?


    utl_smtp.write_data(l_mail_conn, '-----YOURBOUNDARY'||crlf);
    utl_smtp.write_data(l_mail_conn, 'Content-Type: text/plain;'||crlf);
    utl_smtp.write_data(l_mail_conn,  'Content-Transfer-Encoding: 8bit' || crlf);
    utl_smtp.write_data(l_mail_conn,'Content-Disposition: attachment;'|| crlf);
    utl_smtp.write_data(l_mail_conn, ' filename="attach.txt"'|| crlf|| crlf);
FOR rec IN cur_query
   LOOP
      utl_smtp.write_data(l_mail_conn, rec.A1||chr(9)||rec.A2||chr(9)||rec.A3||crlf);
   END LOOP;

   utl_smtp.write_data(l_mail_conn, crlf||'-----YOURBOUNDARY--');


  -- end connectin
    utl_smtp.close_data(l_mail_conn );
    utl_smtp.quit(l_mail_conn);

ie the body output with my code sample would be like:

-----YOURBOUNDARY
Content-Type: text/html

<html><head>
       <style type="text/css">
                   body     { font-family     : Verdana, Arial;
                              font-size       : 10pt;}

                   .green   { color           : #00AA00;
                              font-weight     : bold;}

                   .red     { color           : #FF0000;
                              font-weight     : bold;}

                   pre      { margin-left     : 10px;}

                   table    { empty-cells     : show;
                              border-collapse : collapse;
                              width           : 100%;
                              border          : solid 2px #444444;}

                   td       { border          : solid 1px #444444;
                             font-size       : 12pt;
                              padding         : 2px;}

                   th       { background      : #EEEEEE;
                              border          : solid 1px #444444;
                              font-size       : 12pt;
                              padding         : 2px;}

                   dt       { font-weight     : bold; }

                  </style>
                 </head>
                 <body>test123</body></html>

-----YOURBOUNDARY
Content-Type: text/plain;
Content-Disposition: attachment;
 filename="attach.txt"

help    me  please

-----YOURBOUNDARY--

Upvotes: 1

Related Questions