In the Oracle11g environment, use utl_smtp to create a storage process for sending emails

In the Oracle11g environment, use utl_smtp to create a storage process for sending emails

If there are too many email storage processes on the internet, I will not forward them. Let's make a simple example;

Create or replace procedure Send_mail (mail_body varchar2) is smtp_conn utl_smtp.connection; user_name varchar2 (20): = encrypt ('username @ email.com '); user_paswd varchar2 (20): = encode (utl_encode.base64_encode (utl_raw.cast_to_raw ('Password'); lv_mail_header varchar2 (200): = 'from: username@email.com '| utl_tcp.CRLF |': sanoul@email.com '| utl_tcp.CRLF | 'subject: Oracle Database' | utl_tcp.CRLF; lv_mail_content varchar2 (2000); begin lv_mail_content: = utl_tcp.CRLF | mail_body; smtp_conn: = Merge ('smtp .email.com ', 25); merge (smtp_conn, 'smtp .email.com'); utl_smtp.command (smtp_conn, 'auth login'); utl_smtp.command (smtp_conn, user_name ); -- mail username utl_smtp.command (smtp_conn, user_paswd); -- Mail Password utl_smtp.mail (smtp_conn, '<username@email.com>'); -- Sender email utl_smtp.rcpt (smtp_conn, '<sanoul@email.com> '); -- recipient email token (smtp_conn); token (smtp_conn, token (lv_mail_header); token (smtp_conn, token (lv_mail_content); then (smtp_conn ); exception when others then utl_smtp.quit (smtp_conn); end Send_mail;/-- the stored procedure has been created

Step 2: directly test the function;

Begin send_mail ('test content'); end;/ORA-29278: SMTP temporary error: 421 Service not availableORA-06512: In "SYS. UTL_SMTP ", line 21ORA-06512: In" SYS. UTL_SMTP ", line 97ORA-06512: In" SYS. UTL_SMTP ", line 139ORA-06512: In" SYS. UTL_MAIL ", line 405ORA-06512: In" SYS. UTL_MAIL ", line 594ORA-06512: In line 2

The first time I saw this error, I was shocked because the entire Stored Procedure of mail sending was to directly test the Code with PL/SQL before encapsulating it into the stored procedure, later, I found out that in order to control network permissions in more detail, the Oracle 11g set a separate ACL for access to UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR ).

OK. Step 3: Set the ACL;

-- ACL Step 1: Create BEGIN dbms_network_acl_admin.create_acl (acl => 'httprequestpermission. xml', -- file name, which can be arbitrarily named DESCRIPTION => 'normal access', principal => 'connect ', -- Role is_grant => TRUE, PRIVILEGE => 'connect ', start_date => NULL, end_date => NULL); END;/commit; -- must be submitted;

Then, check whether the ACL control file is created;

SQL> SELECT any_path FROM resource_view WHERE any_path like '/sys/acls/%.xml';

If the created file httprequestpermission. xml is displayed in the list, proceed to step 2 of ACL.

-- ACL Step 2: Authorize the user (in this example, scott is used as the test) begin dbms_network_acl_admin.add_privilege (acl => 'httprequestpermission. xml ', principal => 'Scott', -- user, please change is_grant => TRUE, privilege => 'connect ', start_date => null, end_date => null) according to the actual change ); end ;/

-- ACL Step 3: add the host or domain name begin dbms_network_acl_admin.assign_acl (acl => 'httprequestpermission. xml ', host => 'www .baidu.com', -- http webpage address lower_port => 80, -- http port upper_port => NULL); end;/commit; begin dbms_network_acl_admin.assign_acl (acl => 'httprequestpermission. xml ', host => 'smtp .sina.com.cn', -- smtp server address lower_port => 25, -- smtp port upper_port => NULL); end;/commit;

The last step is to test the stored procedure again.

SQL> begin  2    send_mail(mail_body => 'afafagaga');  3  end;  4  / PL/SQL procedure successfully completed

No error. The email is correctly received. (test environment: Oracle 11.2.0.0, OS: Windows 2008 Server)

(The author has encountered

ORA-24247: network access is rejected by the access control list (ACL;

ORA-29278: SMTP temporary error: 421 Service not available;

ORA-44416: ACL invalid: primary user 'agent' that cannot be parsed'

These three major errors can be solved according to the above steps)