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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.