RE: How to send html using UTL

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 19 Jul 2001 14:00:55 -0700
Message-ID: <F001.0034F40C.20010719135530@fatcity.com>

One of our developers just wrote a package to do just that

PACKAGE HTML_Mail IS

PROCEDURE Send(P_TO IN VARCHAR2,
               P_FROM IN VARCHAR2,
               P_SUBJECT IN VARCHAR2,
               P_CC IN VARCHAR2 DEFAULT NULL,
               P_BCC IN VARCHAR2 DEFAULT NULL,
               P_TEXT IN VARCHAR2 DEFAULT NULL,
               P_HTML IN VARCHAR2);

END HTML_Mail;
PACKAGE BODY HTML_Mail IS

Name: HTML_Mail.Send
--
Description: Send HTML e-mail
--
Parameters: P_TO To Address(es)
P_FROM From Address
P_SUBJECT Subject
P_CC Cc Address(es)
P_BCC Bcc Address(es)
P_TEXT Plain text version of the message
P_HTML HTML text version of the message
--

PROCEDURE Send(P_TO IN VARCHAR2, P_FROM IN VARCHAR2, P_SUBJECT IN VARCHAR2, P_CC IN VARCHAR2 DEFAULT NULL, P_BCC IN VARCHAR2 DEFAULT NULL, P_TEXT IN VARCHAR2, P_HTML IN VARCHAR2) IS boundary VARCHAR2(60) := 'Boundary_(ID_' || 'Boundary_(ID_' || TO_CHAR(SYSDATE, 'MISS_DDHH12_ MMYY_HH24_SSMI') || ')';

txtbody VARCHAR2(32000) := NULL;
msgbody VARCHAR2(32000) := '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>' || p_subject || '</TITLE>
<META content="PL/SQL SID.HTML_Mail.Send" name=GENERATOR></HEAD>
<BODY>';
        BEGIN
                plsql_mail.contact_smtpsrv(P_FROM, RTRIM(P_TO || ';' || P_CC || ';' ||

P_BCC, '; '))
;

                plsql_mail.send_header('From', P_FROM);
                plsql_mail.send_header('To', P_TO);
                IF P_CC IS NOT NULL THEN
                        plsql_mail.send_header('Cc', P_CC);
                END IF;
                plsql_mail.send_header('Subject', P_SUBJECT);
                plsql_mail.send_header('Content-type', 'multipart/alternative;

boundary="' || boundary || '"
This is a multi-part message in MIME format.
--' || boundary || '

Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: 7BIT
' || P_TEXT || '
--' || boundary || '

Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: 7BIT
');

                plsql_mail.send_body(P_HTML || '


--' || boundary || '--');

                plsql_mail.signoff_smtpsrv;
        END Send;

END HTML_Mail;
/

PLSQL_MAIL is a package which interfaces with utl_smtp

create or replace package plsql_mail as

procedure contact_smtpsrv(sender in varchar2, recipients in varchar2); procedure send_header (name in varchar2, header in varchar2); procedure send_body(mail_text in varchar2); procedure signoff_smtpsrv;
end plsql_mail;
/
create or replace package body plsql_mail as

       c utl_smtp.connection;
       procedure contact_smtpsrv(sender in varchar2, recipients in varchar2) is
       token_position number(5,0);
       recipient_string varchar2(2000);
       recipient varchar2(100);

       Begin
           c := utl_smtp.open_connection('<your smtp server');
           utl_smtp.helo(c, 'slac.stanford.edu');
           utl_smtp.mail(c, sender); 
           recipient_string := concat(recipients,';');
           token_position := instr(recipient_string, ';');
               while token_position !=0
               loop
                     recipient := substr(recipient_string,1, token_position -1);
                     recipient_string := substr(recipient_string,token_position +1, 
                                         length(recipient_string) - (token_position 
-1));
                     token_position := instr(recipient_string, ';');
                     utl_smtp.rcpt(c, recipient);
               end loop;
           utl_smtp.open_data(c); 
       EXCEPTION
           WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
              utl_smtp.quit(c);
              raise_application_error(-20000,
              'Failed to send mail due to the following error: ' || sqlerrm);
       end contact_smtpsrv; 
       PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) is
       BEGIN
           utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
       end send_header;
       Procedure send_body(mail_text in varchar2) is
       Begin
          utl_smtp.write_data(c, utl_tcp.CRLF || mail_text);
       end send_body;
       Procedure signoff_smtpsrv is 
       Begin
          utl_smtp.close_data(c);
          utl_smtp.quit(c);
       end signoff_smtpsrv;

end plsql_mail;
/

As always these programs shold be thorougly tested before put into production.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----

Sent: Thursday, July 19, 2001 10:51 AM
To: Multiple recipients of list ORACLE-L

Hi friends :

What can I do for send html text using UTL_SMTP.

I have knowledgement using normal text but not with html.

Regards.
Thanks.

Eriovaldo
eriovaldo.andrietta_at_widesoft.com.br
Brazil

Seja avisado de novas mensagens do Hotmail e use o comunique-se com seus amigos com o MSN Messenger em http://messenger.msn.com.br

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Eca Eca
INET: ecaeser_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

RE: How to send html using UTL_SMTP