Shulou(Shulou.com)05/31 Report--

How to configure mail server in Oracle? Many novices are not very clear about this. In order to help you solve this problem, the following small series will explain it in detail for everyone. Those who have this need can come to learn. I hope you can gain something.

1 Creating ACL

BEGIN dbms_network_acl_admin.create_acl(acl => 'email.xml', DESCRIPTION => 'Enables network permissions for the e-mail server', principal => 'C##ESD_MONITOR', --Indicates which user must be capitalized is_grant => TRUE, PRIVILEGE => 'resolve', start_date => NULL, end_date => NULL);END;

2 Grant authority

begindbms_network_acl_admin.add_privilege(acl => 'email.xml', --ditto xml name principal => 'C##ESD_MONITOR', --Indicates which user must be capitalized is_grant => TRUE, privilege => 'connect', --privilege name start_date => null, end_date => null);end;

3 Set Port

begin dbms_network_acl_admin.assign_acl ( --This command means that users authorized under the acl name utl_sendmail.xml are allowed to access the destination host and its port range using oracle network access packages. acl => 'email.xml', host => '*'-- , -- ip address or domain name, fill in http://localhost:9000/hello and http://localhost:9000/will report invalid host --and it is recommended to use ip address or domain name, if localhost is used, when oracle is not installed on the local machine, there will be problems-- lower_port => 9000, --Allow access to start port number-- upper_port => Null --the number of ports allowed to access); end;

4 Query permission settings

SELECT acl, principal, privilege, is_grant, TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date FROM dba_network_acl_privileges;

5 Create a mail delivery stored procedure

create or replace procedure send_mail(p_recipient VARCHAR2, --mail recipient p_subject VARCHAR2, --Message header p_message VARCHAR2, --Message body p_type number -- 1 text 2html ) as --The following four variables are assigned according to the actual mail server v_mailhost VARCHAR2(30) :smtp.qq.com--SMTP server address v_user VARCHAR2(30) :='111@qq.com';--username to log in to SMTP server v_pass VARCHAR2(20) := '111';--Password authorization code for logging into SMTP server v_sender VARCHAR2(50) := '111@qq.com';--Sender mailbox, generally corresponding to ps_user v_conn UTL_SMTP.connection; --Connection to mail server v_msg varchar2(4000); --Message content BEGIN v_conn := UTL_SMTP.open_connection (v_mailhost, 25); UTL_SMTP.ehlo(v_conn, v_mailhost); --Use ehlo() instead of helo()--Otherwise: ORA-29279: SMTP permanent error: 503 5.5.2 Send hello first. UTL_SMTP.command(v_conn, 'AUTH LOGIN'); -- smtp server login verification UTL_SMTP.command(v_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user)))); UTL_SMTP.command(v_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))); UTL_SMTP.mail(v_conn,''); --Set sender Note: Many materials on the Internet directly write v_sender, which will report ORA-29279: SMTP 500 error UTL_SMTP.rcpt(v_conn,''); --Set recipient UTL_SMTP.open_data(v_conn); --Open stream if p_type = 1 then --Create the content of the message to be sent Note that there should be a blank line between the header information and the message body v_msg := 'Date:' || TO_CHAR(SYSDATE, 'dd mon yy hh34:mi:ss') || UTL_TCP.CRLF || 'From: ' || '' || UTL_TCP.CRLF || 'To: ' || '' || UTL_TCP.CRLF || 'Subject: ' || p_subject || UTL_TCP.CRLF || UTL_TCP.CRLF --This is preceded by header information || p_message; --This is the message body UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg)); --Write title and content like this in Chinese elsif p_type = 2 then UTL_SMTP.write_data(v_conn, 'From:' || '' || utl_tcp.CRLF); UTL_SMTP.write_data(v_conn, 'To:' || '' || utl_tcp.crlf); UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(convert('Subject:' || p_subject || utl_tcp.CRLF, 'ZHS16GBK'))); UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(convert('Content-Type:text/html;charset=GBK' || utl_tcp.CRLF, 'ZHS16GBK'))); UTL_SMTP.write_data(v_conn, utl_tcp.CRLF); UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(convert(p_message, 'ZHS16GBK')); --Write title and content in Chinese end if; UTL_SMTP.close_data(v_conn); --Close stream UTL_SMTP.quit(v_conn); --Close connection EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); END; Is it helpful to read all of the above? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.