Oracle sends Mail

1. Create a stored procedure for sending messages

CREATE OR REPLACE PROCEDURE send_mail (p_recipient VARCHAR2,--Mail recipient
P_subject VARCHAR2,--Mail header
p_message VARCHAR2--Message body
) is
--The following four variables should be assigned according to the actual mail server
v_mailhost VARCHAR2 (+): = ' 123.125.50.135 ';--SMTP server address
V_user VARCHAR2 (30): = ' ****** ';--user name of the login SMTP server
V_pass VARCHAR2 (20): = ' ****** ';--password to log on to the SMTP server
V_sender VARCHAR2 (): = ' ******@163.com ';--sender's mailbox, general and Ps_user correspondence
v_conn utl_smtp.connection;--Connection to the mail server
v_msg varchar2 (4000);--Mail content
BEGIN
V_conn: = Utl_smtp.open_connection (v_mailhost, +);
Utl_smtp.ehlo (V_conn, V_mailhost);--Is using EHLO () instead of the helo () function
--otherwise reported: ORA-29279:SMTP permanent error: 503 5.5.2 Send Hello first.
Utl_smtp.command (v_conn, ' AUTH login ');--SMTP Server login Check
Utl_smtp.command (V_conn,
utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (v_user) ));
Utl_smtp.command (V_conn,
utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (v_pass) ));
utl_smtp.mail (V_conn, ' < ' | | v_sender | | ' > '); --Set Sender
utl_smtp.rcpt (V_conn, ' < ' | | p_recipient | | ' > '); --Set recipient
--Create message content to send note the header message and the message body are blank lines
v_msg: = ' Date: ' | | To_char (sysdate, ' yyyy mm DD hh24:mi:ss ') | |
utl_tcp. CRLF | | ' From: ' | | V_sender | | "| | Utl_tcp. CRLF | |
' to: ' | | | p_recipient | | "| | Utl_tcp. CRLF | | ' Subject: ' | |
P_subject | | Utl_tcp. CRLF | | Utl_tcp. CRLF-This is preceded by a header message
| | p_message;--This is the message body
Utl_smtp.open_data (V_conn);--Open stream
Utl_smtp.write_raw_data (V_conn, Utl_raw.cast_to_raw (v_msg));--so the title and content can be written in Chinese
Utl_smtp.close_data (V_conn);--Close stream
Utl_smtp.quit (V_conn);---Close connection
EXCEPTION
When OTHERS then
Dbms_output.put_line (dbms_utility.format_error_stack);
Dbms_output.put_line (dbms_utility.format_call_stack);
END Send_mail;

2. You need to configure additional permissions to create the stored procedure (the steps created cannot be wrong)

Begin

Dbms_network_acl_admin.create_acl (

ACL = ' sendmail.xml ', --- the name of the access control list created

Description = ' Normal Access ',

Principal = ' CONNECT ',

Is_grant = TRUE,

privilege = ' Connect ',

Start_date = NULL,

End_date = null

);

End

----------------------------------------------------------------

Begin

Dbms_network_acl_admin.add_privilege (ACL = ' sendmail.xml ',

Principal = ' abs_20160104 ',

Is_grant = TRUE,

privilege = ' Connect ',

Start_date = NULL,

end_date = null);

End

----------------------------------------------------------------

Begin

Dbms_network_acl_admin.assign_acl (

ACL = ' Utlpkg.xml ',

Host = ' 123.125.50.135 ', -- access to the server's address

Lower_port =---The lowest port

Upper_port = 25); -- Highest Port

End

----------------------------------------------------------------

--Delete permission file

BEGIN

Dbms_network_acl_admin.drop_acl (ACL = ' sendmail.xml ');

COMMIT;

END;

--Query All-limit documents

SELECT * from Resource_view WHERE any_path like '/sys/acls/%.xml ';

--Querying File network permissions

SELECT host, Lower_port, Upper_port, ACL from DBA_NETWORK_ACLS;

--Querying the user's network permissions

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 Dba_network_acl_privileges;

You can now call the stored procedure to send the message.

Oracle sends Mail