Send mail with Oracle process

the recipient has only one of the following procedures:

CREATE OR REPLACE PROCEDURE P_mail_sina (sender in VARCHAR2,--Sender
Recipient in VARCHAR2,--Receiving person
Subject in VARCHAR2,--Email subject
Message in VARCHAR2) is--Email content
Mailhost VARCHAR2 (+): = ' smtp.sina.com ';--Sina mail server
c Utl_smtp.connection;
Msg VARCHAR2 (1000);
BEGIN
msg: = ' Date: ' | | To_char (SYSDATE-1, ' dd Mon yy hh24:mi:ss ') | |
Utl_tcp.crlf | | ' From: < ' | | Sender | | ' > ' | | Utl_tcp.crlf | |
' Subject: ' | | Subject | | Utl_tcp.crlf | | ' To: < ' | | Recipient | | ' > ' | |
Utl_tcp.crlf | | "| | Utl_tcp.crlf | | Message
c: = Utl_smtp.open_connection (Mailhost, 25);
Utl_smtp.command (c, ' auth login ');--1
Utl_smtp.command (c,--2
utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (' username ' )));--3--Send mailbox user name
Utl_smtp.command (c,--4
utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (' Password ' )));--5--Send email password
Utl_smtp.helo (c, mailhost);
Utl_smtp.mail (c, sender);
UTL_SMTP.RCPT (c, recipient);
Utl_smtp.open_data (c);
Utl_smtp.write_raw_data (c, Utl_raw.cast_to_raw (msg));
Utl_smtp.close_data (c);
Utl_smtp.quit (c);
EXCEPTION
When Utl_smtp.transient_error OR Utl_smtp.permanent_error then
BEGIN
Utl_smtp.quit (c);
EXCEPTION
When OTHERS Then
NULL;
END;
When OTHERS Then
Dbms_output.put_line (SQLERRM);
END P_mail_sina;

Precautions:

1. Because in the test I use the external network way to send incoming mail, it must be added with this part of the code circled out to authenticate. Intra-intranet delivery is not required, but not tested.

2. * * * This part of the code circled in the third and fifth lines of the content is the mailbox server corresponding mailbox user name and password.

3. The server that is used to send the mailbox must create an ACL, mostly three steps

1) Create an access control list ACL

BEGIN

Dbms_network_acl_admin. Create_acl (
ACL = ' Email_server_permissions.xml ',
Description = ' Enables network permissions for the e-mail server ',
Principal = ' Database_user ',--database user to operate and capitalize the user
Is_grant = TRUE,
privilege = ' Connect ');
END;

2) the ACL and the Mail Server association (this step, the use of different mailbox server will need to be executed separately, if you want to use QQ mailbox to send, then you must be replaced by QQ Mailbox server to do it again)

BEGIN
Dbms_network_acl_admin.assign_acl (
ACL = ' Email_server_permissions.xml ',
Host = ' smtp.sina.com ',/* SINA's mailbox server address, if it is sent by QQ as Mail,

Then use QQ Mailbox server address smtp.qq.com*/
Lower_port = 25,
Upper_port = NULL);
COMMIT;
END;

After association, you can use DBA_NETWORK_ACLS to see if the generated

SELECT host, Lower_port, Upper_port, ACL from SYS.DBA_NETWORK_ACLS;

3) grant permission to connect to the mail server for the executing database user

BEGIN
Dbms_network_acl_admin.add_privilege (
ACL = ' Email_server_permissions.xml ',
Principal = ' Database_user ',--database user to operate and capitalize the user

Is_grant = TRUE,
privilege = ' Connect ');
END;

View granted permissions with the Dba_network_acl_privileges view

SELECT ACL,
Principal
Privilege
Is_grant,
To_char (start_date, ' dd-mon-yyyy ') as Start_date,
To_char (end_date, ' dd-mon-yyyy ') as End_date
From Sys.dba_network_acl_privileges;

4.utl_tcp.crlf Effect: line break

Note in the message header, sender, recipient, message content, there must be a newline between each, otherwise it will not be sent or the message content is empty.

5. Chinese garbled problem

Utl_smtp.write_raw_data Use this procedure to replace utl_smtp.write_data can solve the problem of Chinese garbled.

6. Self-test found that if the use of QQ mailbox to send mail, then

Utl_smtp.helo (c, Mailhost), the statement must be placed before utl_smtp.command, otherwise cannot send, but Sina mailbox does not matter, this somehow.???

Send to more than one person

CREATE OR REPLACE PROCEDURE p_sendmailto_many (sender in VARCHAR2,
Recipient1 in VARCHAR2,
Recipient2 in VARCHAR2,
Recipient3 in VARCHAR2,
Subject in VARCHAR2,
Message in VARCHAR2) is
Mailhost VARCHAR2 (+): = ' smtp.qq.com ';
c Utl_smtp.connection;
Msg VARCHAR2 (1000);
BEGIN
msg: = ' Date: ' | | To_char (SYSDATE-1, ' dd Mon yy hh24:mi:ss ') | |
Utl_tcp.crlf | | ' From: < ' | | Sender | | ' > ' | | Utl_tcp.crlf | |
' Subject: ' | | Subject | | Utl_tcp.crlf | | ' To: < ' | | Recipient1 | |
' >;< ' | | Recipient2 | | ' > ' | | Utl_tcp.crlf | | ' Cc: < ' | |
Recipient3 | | ' > ' | | Utl_tcp.crlf | | "| | Utl_tcp.crlf | |
Message
c: = Utl_smtp.open_connection (Mailhost, 25);
Utl_smtp.helo (c, mailhost);
Utl_smtp.command (c, ' auth login ');
Utl_smtp.command (c,
Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (sender)));
Utl_smtp.command (c,
Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (' password ')));
Utl_smtp.mail (c, sender);
UTL_SMTP.RCPT (c, recipient1);
UTL_SMTP.RCPT (c, Recipient2);
UTL_SMTP.RCPT (c, Recipient3);
Utl_smtp.open_data (c);
Utl_smtp.write_raw_data (c, Utl_raw.cast_to_raw (msg));
Utl_smtp.close_data (c);
Utl_smtp.quit (c);
END P_sendmailto_many;

This article is from the "KINGDBA" blog, make sure to keep this source http://kingdba.blog.51cto.com/801693/1441728

Send mail with Oracle process