Send Oracle mail
Before 10G, if you want to send email from oracle, you must use oracle's utl_smtp and utl_tcp function packages to package an email sender and then use it. Since 10G, this process will become very simple. Because oracle provides us with a utl_mail package, we can directly call send email. In fact, the bottom layer of this package also calls the relevant api of utl_smtp and utl_tcp. However, utl_mail can only be used on smtp servers without security authentication. If the smtp server requires security authentication, it can only be implemented with utl_smtp.
1. Determine whether utl_mail is installed, if you do not install utl_mail with the following statement
@% ORACLE_HOME%\ rdbms\ admin\ utlmail.sql
@% ORACLE_HOME%\ rdbms\ admin\ prvtmail.plb
2. Set smtp_out_server parameters
Oracle added this parameter to define the smtp server in 10g. If this parameter is not set, oracle will automatically parse the db_domain parameter and use the domain name to send mail. If there is no db_domain and no setting, mail will not be sent successfully. It is recommended to set the smtp_out_server parameter:
Alter system set smtp_out_server = 'smtp.chengmail.cn'
If you want to set up multiple smtp servers at the same time, you can separate each server with a comma:
Alter system set smtp_out_server = 'smtp.chengmail.cn, mail.a.com'
The above domain name can also be replaced by ip. Oracle uses port 25 to send by default, or you can specify a specific port manually:
Alter system set smtp_out_server = 'smtp.chengmail.cn:25, mail.a.com:25'
3. Test email delivery
Currently, utl_mail provides three processes for sending mail.
PROCEDURE SEND
Parameter name type input / output default value?
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
REPLYTO VARCHAR2 IN DEFAULT
PROCEDURE SEND_ATTACH_RAW
Parameter name type input / output default value?
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 DEFAULT
REPLYTO VARCHAR2 IN DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
Parameter name type input / output default value?
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
REPLYTO VARCHAR2 IN DEFAULT
Create a directory for attachments:
Create directory export as'/ oracle/product/dump_dir'
Grant read, write on directory export to public
Example of sending a message without attachments:
Begin
Utl_mail.send (sender = > 'oracle@scmdbserver'
Recipients = > 'ypma@ique.com'
Subject = > 'oracle email test'
Cc = > 'gsun@ique.com, pzhang@ique.com'
Message = > 'sender ok?')
End
/
Send messages with attachments (text-based attachments). To send binary attachments, you can use the utl_mail.send_attach_raw procedure:
DECLARE
FHandle utl_file.file_type
VTextOut varchar2 (32000)
Text varchar2 (32000)
BEGIN
FHandle: = UTL_FILE.FOPEN ('EXPORT',' a.logically,'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'
Message = > 'sender ok?'
Attachment = > text
Att_filename = > 'a.log')
END
/
Oracle uses varchar2 to store the contents of attachments in send_attach_varchar2, that is, attachments cannot be larger than 32k. Send_attach_raw cannot send attachments that exceed 2000 bytes.
