Oracle process email

Many people have explained this online, the relevant code is also a lot, the code reference http://hi.baidu.com/bailang3106/item/c80b0fed8a08dbd1ea34c952

Http://baser.blog.51cto.com/4153192/871086

 

There is only one recipient process:

 

Create or replace procedure p_mail_sina (sender in varchar2, -- Sender
Recipient in varchar2, -- recipient
Subject in varchar2, -- mail subject
Message in varchar2) is -- mail content
Mailhost varchar2 (30): = '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 -- sending email Username
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;

Note:

1. In the test, I used the Internet to send and receive emails. Therefore, the Code circled in yellow must be added for authentication. Intranet sending is not required, but it is not tested.

2. The third line and fifth line of the Code circled in yellow are respectively the mailbox username and password corresponding to the mailbox server.

3. The server used to send the email address must create an ACL. There are 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', -- the database user that performs the operation and the user must be capitalized
Is_grant => true,
Privilege => 'connect ');
End;

2) associate the ACL with the email server (in this step, different email servers need to be executed separately. If you want to use QQ mail, you must change it to QQ mail server for execution)

 

Begin
Dbms_network_acl_admin.assign_acl (
ACL => 'email _ server_permissions.xml ',
Host => 'smtp .sina.com ',/* address of the Sina mail server. If QQ is used as the mail,

Use the QQ mail server address smtp.qq.com */
Lower_port => 25,
Upper_port => null );
Commit;
End;

After Association, you can use dba_network_acls to check whether the node has been generated.

 

Select host, lower_port, upper_port, ACL from SYS. dba_network_acls;

 

 

3) authorize the database user to connect to the mail server

 

 

Begin
Dbms_network_acl_admin.add_privilege (
ACL => 'email _ server_permissions.xml ',
Principal => 'database _ user', -- the database user that performs the operation and the user must be capitalized

Is_grant => true,
Privilege => 'connect ');
End;

View the granted permissions in 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 function: line feed

Note that there must be a line break between the email title, sender, recipient, and content. Otherwise, the email cannot be sent or the content is blank.

 

5. Chinese garbled characters

Utl_smtp.write_raw_data can replace utl_smtp.write_data to solve Chinese garbled characters.

 

6. I found in my own test that if I use QQ mail to send emails

Utl_smtp.helo (C, mailhost); the statement must be placed before utl_smtp.command; otherwise, it cannot be sent, but Sina mail does not matter, this somehow. ???

 

 

Send to multiple people

Create or replace procedure p_sendmailto_loads (sender in varchar2,
Recipient1 in varchar2,
Recipient2 in varchar2,
Recipient3 in varchar2,
Subject in varchar2,
Message in varchar2) is
Mailhost varchar2 (30): = '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;