Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.