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