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

Too many emails stored on the internet, I do not forward, get a simple as an example;

Create or Replace procedure Send_mail (Mail_body varchar2) is Smtp_conn utl_smtp.connection; User_name VARCHAR2 (m): = Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode utl_raw.cast_to_raw (' 
  Username@email.com '));
  USER_PASWD Varchar2 (a): = Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (' password ')); Lv_mail_header varchar2: = ' from:username@email.com ' | | Utl_tcp.
                                crlf| | ' To: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: = 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); --Mail password utl_smtp.mail (smtp_conn, ' <username@email.com> '); --Sender's mailbox utl_smtp.rcpt (Smtp_conn, ' <sanoul@email.com> '));

  --Recipient Mailbox Utl_smtp.open_data (SMTP_CONN);
  Utl_smtp.write_raw_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)); -By the way, the maximum length of the Utl_raw.cast_to_raw is 16383, if your message body is oversized, please loop through the body write_raw_data--otherwise you will get Ora-06502:pl/sql: numeric or value errors Utl_
  Smtp.close_data (Smtp_conn);
Utl_smtp.quit (Smtp_conn);
Exception when others then Utl_smtp.quit (smtp_conn);
End Send_mail; /--The stored procedure has been created

The second step is to test the function directly;

Begin
  Send_mail (' Test content ');
End;
/

ORA-29278:SMTP Temporary error: 421 Service not available
ORA-06512: in the SYS. Utl_smtp ", line
ORA-06512: in the SYS. Utl_smtp ", line
ORA-06512: in the SYS. Utl_smtp ", line 139
ORA-06512: In" SYS. Utl_mail ", line 405
ORA-06512: In" SYS. Utl_mail ", line 594
ORA-06512: In line 2

The first time I saw this error was very shocking, because the entire mail sent stored procedures are first used pl/sql directly test the code, then encapsulated into the stored procedure, and then after the search to know more carefully to control network permissions, Oracle 11g for Utl_tcp, UTL_SMTP, Utl_mail, Utl_http and UTL_INADDR access are set up with separate permission access control (ACLs).

OK, step three, set ACLs;

--acl The first step, create the
BEGIN  
        dbms_network_acl_admin.create_acl (ACL         => ' Httprequestpermission.xml ',  - File name, you can 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 httprequestpermission.xml you just created appears in the list, continue with the ACL step two

--acl The second step, the authorized user (sample Scott as 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 step three, add a 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;

The last is to test the stored procedure again

Sql> begin
  2    send_mail (mail_body => ' Afafagaga ');
  3 End  ;
  4  /
 
Pl/sql procedure successfully completed

There is no error, the message received correctly; (This test environment: Oracle 11.2.0.0,os:windows 2008 Server)

(the author has encountered during the test

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

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

Invalid ORA-44416:ACL: Unable to resolve the primary user ' AGENT '

These three major errors can be addressed by the above steps.