CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
K.I
K.I

Reputation: 588

Oracle PL/SQL send HTML mail does not behave as expected

I am trying to send mail using Oracles UTL_SMTP. It works, but I get weird behaviour and I don't know why. When I send mail using my code below HTML code is printed with all the tags. Another problem, header information such as the sender and content type is also printed. I have been looking at this code for couple of hours now, but failed to figure out what the problem is.

Please Help!

  l_mail_conn UTL_SMTP.connection;
  l_mail_conn := UTL_SMTP.open_connection('myhost', '25');
  UTL_SMTP.helo(l_mail_conn, 'myhost');
  UTL_SMTP.mail(l_mail_conn, '[email protected]');
  UTL_SMTP.rcpt(l_mail_conn, '[email protected]');

  UTL_SMTP.open_data(l_mail_conn);
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: [email protected]' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: [email protected]'|| UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: test' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: [email protected]' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, '<html>
    <head>
      <title>Test HTML message</title>
    </head>
    <body>
      <p>This is a <b>HTML</b> <i>version</i> of the test message.</p>
      <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />
    </body>
  </html>');
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);

And this is the e-mail I get when I send mail using this code:

Content-Type: text/html; charset="iso-8859-1"

<html>
    <head>
      <title>Test HTML message</title>
    </head>
    <body>
      <p>This is a <b>HTML</b> <i>version</i> of the test message.</p>
      <p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />
    </body>
  </html>

Upvotes: 0

Views: 3141

Answers (1)

Daniel Vukasovich
Daniel Vukasovich

Reputation: 1742

you are missing just a couple of lines.

For sending html content, you are missing: 

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_html_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

Where l_boundary is a boundary varchar2 and p_html_msg is your html code.

For attachments: 

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
    END LOOP;

UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

Where l_step is a PLS_INTEGER (multiple of 3, for instance 12000) and p_attach_blob is your attachment (file).

And update this part of your code adding the following #2 lines: 

 UTL_SMTP.open_data(l_mail_conn);
 UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'To: [email protected]' || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'From: [email protected]'|| UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'Subject: test' || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'Reply-To: [email protected]' || UTL_TCP.crlf || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
 UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

Where l_boundary is a boundary varchar2 mentioned above.

Here you have your code updated:

GIST PL/SQL updated

And that's it.

Regards

Upvotes: 3

Related Questions