Use UTL_SMTP to create stored procedures for sending messages in Oracle 11g environment

Online too many e-mail storage process, I will not forward, to get a simple example;

Create or Replace procedure Send_mail (Mail_body varchar2) is Smtp_conn utl_smtp.connection; User_name VARCHAR2: = Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (' [email    protected]));  USER_PASWD varchar2: = Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (' password '))); Lv_mail_header varchar2 ($): = ' from:[email protected] ' | | Utl_tcp.                                crlf| | ' To:[email protected ' | | Utl_tcp.                                crlf| | ' Subject:oracle database ' | | Utl_tcp.  CRLF; Lv_mail_content varchar2 (+); begin lv_mail_content: = Utl_tcp. crlf| |    Mail_body;  Smtp_conn: = utl_smtp.open_connection (' smtp.email.com ', 25);  Utl_smtp.helo (Smtp_conn, ' smtp.email.com ');  Utl_smtp.command (Smtp_conn, ' AUTH LOGIN '); Utl_smtp.command (Smtp_conn,user_name); --Mail user name Utl_smtp.command (SMTP_CONN,USER_PASWD); --Email password utl_smtp.mail (smtp_conn, ' <[email protected]> '); --Sender Mailbox Utl_smtp.rcpt (Smtp_conn, ' <[email protecteD]> ');  --Recipient Mailbox Utl_smtp.open_data (SMTP_CONN);   Utl_smtp.write_data (Smtp_conn,utl_raw.cast_to_raw (Lv_mail_header));  Utl_smtp.write_raw_data (Smtp_conn,utl_raw.cast_to_raw (lv_mail_content));  Utl_smtp.close_data (Smtp_conn); Utl_smtp.quit (smtp_conn); exception when others then Utl_smtp.quit (smtp_conn); end send_mail;/--Stored procedure created

The second step is to test the function directly;

Begin  Send_mail (' Test content '); end;/ora-29278:smtp temp 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: On line 2

The first time I saw this error was very shocking, because the entire message sent by the stored procedure is first used in PL/SQL directly after testing the code, then encapsulated in the stored procedure, later after the search to know in order to more granular control network permissions, Oracle 11g for Utl_tcp, UTL_SMTP, Utl_mail, Utl_http and UTL_INADDR access are set up with separate access control methods (ACLs).

OK, step three, set the ACL;

--acl first step, create the Begin          Dbms_network_acl_admin.create_acl (ACL         = ' httprequestpermission.xml ',  --file name, can be any name                                          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 was created;

Sql> SELECT Any_path from Resource_view WHERE any_path like '/sys/acls/%.xml ';

If the file you just created appears in the listHttprequestpermission.xml, continue with ACL step two

--acl The second step, the authorized user (example with Scott as the test) begin          Dbms_network_acl_admin.add_privilege (ACL        = ' Httprequestpermission.xml ',                                               principal  = ' SCOTT ',  --user, please follow the actual change                                             is_grant   = TRUE,                                               Privilege  = ' Connect ',                                               start_date = null,                                               end_date   = null);  End;  /

--acl The third step, add the host or domain name    begin          Dbms_network_acl_admin.assign_acl (ACL        = ' Httprequestpermission.xml ',                                            host       = ' www.baidu.com ',  --http web address                                          lower_port =,  --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 =,  --smtp port                                          upper_port = NULL);      End;  /commit;

And the end is to test the stored procedure again

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

There are no errors, the message is received correctly; (test environment in this article: Oracle 11.2.0.0,os:windows Server)

(During the test, the author has encountered

ORA-24247: Network access is denied by Access control List (ACL);

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

Ora-44416:acl Invalid: Unable to resolve the primary user ' AGENT '

These three major errors, as described above, can solve these problems)