In the Oracle 11g environment, you can use utl_smtp to create a storage process for sending mails, 11gutl_smtp

In the Oracle 11g environment, you can use utl_smtp to create a storage process for sending mails, 11gutl_smtp

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)

How to Use utl_smtp to send an email from Oracle (1)

(Note: If your application is built on the basis of Oracle 8i, the premise is that the operation and maintenance is in Oracle 10 Gb or later versions, you can also use the old utl_smtp toolkit to send emails .) One advantage of utl_smtp code is that it can run normally on 10 Gb of Oracle, so we do not need to replace utl_smtp toolkit with utl_mail. Although the day when utl_mail completely replaces utl_smtp will undoubtedly come, utl_smtp can still meet our needs. First, confirm that the utl_smtp toolkit has been installed in your system (of course, in the SYS architecture ). If you have not installed this toolkit, you can find the utlsmtp. SQL script in your ORACLE_HOME \ RDBMS \ admin folder. You also need the utl_tcp package. Similarly, if you find that the utl_tcp package has not been loaded, you can find the utltcp. SQL script in the same path as the utlsmtp. SQL script. Finally, you need to know the URL of your enterprise SMTP server. (Note: The following example does not apply to servers that have security settings for SMTP, for example, Gmail.) The package description statement is as follows: create or replace PACKAGE sendmail IS procedure send (p_sender varchar2, p_recipient varchar2, p_subject varchar2, p_body varchar2 default null); end sendmail; carefully observe the subject of the preceding statement and the following PACKAGE, you may find that the public method of send depends on the private method called common, so that we can expand this package later, it also shows you how to send Binary Large Object (blob) attachments. For example, if you generate a PDF document and save it in your database, you can send it as an attachment to an email, the common mode is used to complete this task. The code to be used comes from the basic send mode and send_blob mode. The following IS the main BODY of the PACKAGE: create or replace package body sendmail IS procedure common (p_sender varchar2, p_recipient varchar2, p_subject varchar2, c out utl_smtp.connection) is v_recipient varchar2 (1000); begin -- make connection to smtp c: = utl_smtp.open_connection ('smtp .example.com '); -- identify the domain of the sender utl_smtp.helo (c, 'example. com '); -- start a mail, specify the sender utl_smtp.mail (c, p_sender); -- identify recipient utl_smtp.rcpt (c, v_recipient); -- start the mail body utl_smtp.open_data (c ); utl_smtp.write_data (c, 'From: '| p_sender | utl_tcp.crlf); utl_smtp.write_data (c, 'to: & #39 ...... remaining full text>

How to use stored procedures to send emails

------------------------------------------------ Write the mail header and content ------------------------------------------
PROCEDURE WRITE_DATA (P_CONN in out nocopy UTL_SMTP.CONNECTION,
P_NAME IN VARCHAR2,
P_VALUE IN VARCHAR2,
P_SPLITE VARCHAR2 DEFAULT ':',
P_CRLF VARCHAR2 DEFAULT L_CRLF) IS
BEGIN
/* Utl_raw.cast_to_raw is very important for Solving Chinese garbled characters */
UTL_SMTP.WRITE_RAW_DATA (P_CONN, UTL_RAW.CAST_TO_RAW (CONVERT (P_NAME |
P_SPLITE |
P_VALUE |
P_CRLF, 'zhs16gbk ')));
END;
---------------------------------------- Write the MIME mail tail -----------------------------------------------------

PROCEDURE END_BOUNDARY (conn in out nocopy UTL_SMTP.CONNECTION,
Last in boolean default false) IS
BEGIN
UTL_SMTP.WRITE_DATA (CONN, UTL_TCP.CRLF );
IF (LAST) THEN
UTL_SMTP.WRITE_DATA (CONN, LAST_BOUNDARY );
End if;
END;

---------------------------------------------- Send an attachment ----------------------------------------------------

Procedure attachment (conn in out nocopy UTL_SMTP.CONNECTION,
MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain ',
Inline in boolean default true,
FILENAME... the remaining full text>