DanBrother的部落格

Method 1:  Using Customized SEND_MAIL Procedure

create procedure send_mail
(sender     VARCHAR2 default null,         -- 寄件者
recipient  VARCHAR2 default null,         -- 收件者
cc_recipient VARCHAR2 default null,    -- 副本
bcc_recipient VARCHAR2 default null,  -- 密件副本
subject    VARCHAR2 default null,     -- 主旨
message    VARCHAR2 default null   --  內容(可以用HTML的格式)
)
is
mail_conn utl_smtp.connection;  -- declare mail object
CRLF varchar2(2) := CHR( 13 ) || CHR( 10 );
v_subject varchar2(255):=subject;
v_message varchar2(4000):=message;

begin
 -----------------------------------------------
 -- Open Mail Connection Object
 -----------------------------------------------
 mail_conn := utl_smtp.open_connection(' ');
 
 ----------------------------------
 -- Establish Connection
 ----------------------------------
 utl_smtp.helo(mail_conn, ' ');
 
 -----------------------
 -- Setup Sender
 -----------------------
 utl_smtp.mail(mail_conn, sender);
 
 ---------------------------
 -- Setup Recipient
 ---------------------------
 utl_smtp.rcpt(mail_conn, recipient); 
 utl_smtp.rcpt(mail_conn, cc_recipient);
 utl_smtp.rcpt(mail_conn, bcc_recipient); 

 --------------------------------
 -- Write Subject
 --------------------------------
 utl_smtp.open_data(mail_conn); 
 v_subject:='Subject:'||v_subject||CRLF;
 utl_smtp.write_raw_data( mail_conn, utl_raw.cast_to_raw(convert(v_subject,'ZHT16BIG5')));  
 utl_smtp.write_data( mail_conn, 'MIME-Version: 1.0' || CRLF ); 
 utl_smtp.write_data( mail_conn, 'Content-Type: text/html; charset=big5' || CRLF ); 
 utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );
 
 ------------------------------------------
 -- Write Mail Body (Message)
 ------------------------------------------
 utl_smtp.write_raw_data( mail_conn, utl_raw.cast_to_raw(convert(v_message,'ZHT16BIG5')) );
 
 -----------------------------
 -- Close Connection
 -----------------------------
 utl_smtp.close_data(mail_conn); 
 utl_smtp.quit(mail_conn);
 
EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
         utl_smtp.quit(mail_conn);
         raise_application_error(-20000,
         'Failed to send mail due to the following error: ' ||sqlerrm);
end;


Method 2:  Using UTL_MAIL.SEND Procedure

utl_mail.send(sender => 'sender@domain.tw',
                        recipients =>'A1@domain.com.tw,A2@domain.com.tw,A3@domain.com.tw',
                        cc => NULL,
                        bcc => NULL,
                        subject => 'TEST SUBJECT',
                        message => 'TEST MESSAGE, 
                        mime_type => 'text/html; charset=UTF-8',
                        priority => 1);

To install UTL_MAIL:

• sqlplus sys/<pwd>

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

[Reference]
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001699