Easily send email-utl_mail in Oracle 10 GB

If you want to send an email from Oracle before 10 Gb, you must use the utl_smtp and utl_tcp feature packages of Oracle to package an email sender program and then use it. since 10 Gb, this process will become very simple. because Oracle provides us with an utl_mail package, we can directly call and send an email. in fact, the underlying layer of this package also calls APIs related to utl_smtp and utl_tcp. however, utl_mail can only be used on stmp servers without security authentication. if the SMTP server requires security authentication, only utl_smtp can be used.

1. Check whether utl_mail is installed. If the following statement is not used to install utl_mail

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

2. Set the smtp_out_server Parameter

Oracle added this parameter in 10 Gb to define the SMTP server. if this parameter is not set, Oracle will automatically resolve the db_domain parameter and use the domain name to send mail. If it is not set without db_domain, mail will not be sent successfully. we recommend that you set the smtp_out_server parameter.

  alter system set smtp_out_server='mail.a.com';

If you want to set up multiple SMTP servers at the same time, you can separate them with commas.

  alter system set smtp_out_server='mail.a.com:25,mail.b.com';

The above domain name can also be replaced by an IP address. By default, Oracle uses port 25 for sending, or you can manually specify a specific port.

  alter system set smtp_out_server='mail.a.com:25,mail.b.com:25';

3. test email sending

Currently, utl_mail provides three email sending processes.

 SQL> desc utl_mailPROCEDURE SEND Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER                         VARCHAR2                IN RECIPIENTS                     VARCHAR2                IN CC                             VARCHAR2                IN     DEFAULT BCC                            VARCHAR2                IN     DEFAULT SUBJECT                        VARCHAR2                IN     DEFAULT MESSAGE                        VARCHAR2                IN     DEFAULT MIME_TYPE                      VARCHAR2                IN     DEFAULT PRIORITY                       BINARY_INTEGER          IN     DEFAULTPROCEDURE SEND_ATTACH_RAW Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER                         VARCHAR2                IN RECIPIENTS                     VARCHAR2                IN CC                             VARCHAR2                IN     DEFAULT BCC                            VARCHAR2                IN     DEFAULT SUBJECT                        VARCHAR2                IN     DEFAULT MESSAGE                        VARCHAR2                IN     DEFAULT MIME_TYPE                      VARCHAR2                IN     DEFAULT PRIORITY                       BINARY_INTEGER          IN     DEFAULT ATTACHMENT                     RAW                     IN ATT_INLINE                     BOOLEAN                 IN     DEFAULT ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT ATT_FILENAME                   VARCHAR2                IN     DEFAULTPROCEDURE SEND_ATTACH_VARCHAR2 Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- SENDER                         VARCHAR2                IN RECIPIENTS                     VARCHAR2                IN CC                             VARCHAR2                IN     DEFAULT BCC                            VARCHAR2                IN     DEFAULT SUBJECT                        VARCHAR2                IN     DEFAULT MESSAGE                        VARCHAR2                IN     DEFAULT MIME_TYPE                      VARCHAR2                IN     DEFAULT PRIORITY                       BINARY_INTEGER          IN     DEFAULT ATTACHMENT                     VARCHAR2                IN ATT_INLINE                     BOOLEAN                 IN     DEFAULT ATT_MIME_TYPE                  VARCHAR2                IN     DEFAULT ATT_FILENAME                   VARCHAR2                IN     DEFAULT

Create a directory for storing attachments.

  create directory export as '/oracle/product/dump_dir';  grant read,write on directory export to public;

Example of email sending without attachment:

begin utl_mail.send(sender=>'oracle@scmdbserver',               recipients=>'ypma@ique.com',               subject=>'oracle email test',               cc=>'gsun@ique.com,pzhang@ique.com',               bcc=>'gsun@ique.com,pzhang@ique.com',               message=>'sender ok?');end;/

Emails with attachments (text-based attachments). To send binary attachments, you can use utl_mail.send_attach_raw.

DECLARE    fHandle utl_file.file_type;    vTextOut varchar2(32000);    text varchar2(32000);BEGIN    fHandle := UTL_FILE.FOPEN('EXPORT','a.log','r');    IF UTL_FILE.IS_OPEN(fHandle) THEN      DBMS_OUTPUT.PUT_LINE('File read open');    ELSE     DBMS_OUTPUT.PUT_LINE('File read not open');    END IF;    LOOP     begin        UTL_FILE.GET_LINE(fHandle,vTextOut);        text:=text||vTextOut;     EXCEPTION        WHEN NO_DATA_FOUND THEN EXIT;     end;     END LOOP;   UTL_FILE.FCLOSE(fHandle);   utl_mail.send_attach_varchar2(sender=>'oracle@scmdbserver',                                 recipients=>'ypma@ique.com',                                 subject=>'oracle email test',                                 cc=>'gsun@ique.com,pzhang@ique.com',                                 bcc=>'gsun@ique.com,pzhang@ique.com',                                 message=>'sender ok?',                                 attachment=>text,                                 att_filename=>'a.log');END;/

Oracle uses varchar2 to store the attachment content in send_attach_varchar2. That is to say, the attachment cannot be larger than 32k. send_attach_raw and cannot send attachments larger than 2000 bytes.

For information about Oracle mail sending, see Metalink: Doc ID:269375.1Doc ID201639.1FAQ and known issues while using utl_smtp and utl_mail DOC: ID730746.1This includes the method for sending attachments larger than 32 K

 

From: http://www.validba.net/2009/06/oracle-10g%E4%B8%AD%E8%BD%BB%E6%9D%BE%E5%8F%91%E9%80%81email-%E5%8E%9F%E5%88%9B/

 

Ref: http://hi.baidu.com/javenzhen/blog/item/bed3b74a8f7c63e383025c77.html