Send Email via Oracle

This article is an example of sending an Email through Oracle10g. Procedure supports Chinese emails, HTML code, and multiple recipients.
In Procedure, p_receiver is the recipient address. Multiple recipients are separated by commas,
P_sub is the mail title, and p_txt is the mail content (HTML is supported ). The Code is as follows:

Create or replace procedure bsd_sendemail (p_receiver varchar2, p_sub varchar2, p_txt varchar2) is p_user varchar2 (30): = ''; p_pass varchar2 (30): = ''; p_sendor varchar2 (20): = 'gni'; p_server varchar2 (20): = 'mail _ Server_IP '; p_port number: = 25; p_need_smtp number: = 0; p_subject varchar2 (4000); l_crlf varchar2 (2): = utl_tcp.crlf; l_sendoraddress varchar2 (4000); l_splite varchar2 (10): = '++'; bou Ndary constant varchar2 (256): = '----- BYSUK'; first_boundary constant varchar2 (256): = '--' | boundary | l_crlf; last_boundary constant varchar2 (256 ): = '--' | boundary | '--' | l_crlf; multipart_mime_type constant varchar2 (256): = 'multipart/mixed; boundary = "'| boundary |'" '; type address_list is table of varchar2 (100) index by binary_integer; my_address_list address_list ;-------------- Segment separated email addresses segment procedure p_splite_str (p_str varchar2, p_splite_flag int default 1) is l_addr varchar2 (254): = ''; l_len int; l_str varchar2 (4000); j int: = 0; -- indicates the number of email addresses or attachments. begin/* indicates the list of processed email addresses, including removing spaces and converting; to, and so on */l_str: = trim (rtrim (replace (p_str, ';', ','), '',''), ','); l_len: = length (l_str); for I in 1 .. l_len loop if subst R (l_str, I, 1) <> ', 'then l_addr: = l_addr | substr (l_str, I, 1); else j: = j + 1; if p_splite_flag = 1 then -- it indicates the email address to be processed. '<>' must be added before and after the address. Otherwise, many mailboxes cannot send emails to Rochelle ADDR: = '<' | l_addr | '>'; -- call my_address_list (j): = l_addr; end if; l_addr: = ''; end if; if I = l_len then j: = j + 1; if p_splite_flag = 1 then -- call the mail sending process l_addr: = '<' | l_addr | '> '; my_address_list (j): = l_addr; end if; end Loop; end; begin write the email header and content into 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_spli Te | p_value | p_crlf, 'zhs16cgb231280 '); end; begin write the mime mail tail into 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; begin ;----------------------------------- ---------- Initiate procedure p_email (p_sendoraddress2 varchar2, -- send address p_receiveraddress2 varchar2) -- accept address is l_conn utl_smtp.connection; -- Define connection begin/* to initialize the mail server information, connect to the email server */l_conn: = listen (p_server, p_port); utl_smtp.helo (l_conn, p_server);/* smtp server logon verification */if p_need_smtp = 1 then utl_smtp.command (l_conn, 'auth login', ''); utl_smtp. Command (l_conn, convert (utl_raw.cast_to_raw (p_user); utl_smtp.command (l_conn, aggregate (utl_raw.cast_to_raw (p_pass); end if; /* set the Sending address and Receiving address */utl_smtp.mail (l_conn, p_sendoraddress2); utl_smtp.rcpt (l_conn, p_receiveraddress2);/* set the mail header */accept (l_conn ); /* set the Date */-- write_data (l_conn, 'date', to_char (sy Sdate-1/3, 'dd Mon yy hh24: mi: ss');/* Set sender */write_data (l_conn, 'from', p_sendor ); /* Set recipient */write_data (l_conn, 'to', p_receiver);/* Set Email Subject */select replace ('=? GB2312? B? '| Utl_raw.cast_to_varchar2 (utl_encode.base64_encode (rawtohex (p_sub) | '? = ', Utl_tcp.crlf ,'')
Into p_subject from dual; write_data (l_conn, 'subobject', p_subject); write_data (l_conn, 'content-type', multipart_mime_type); terminate (l_conn, struct); terminate (l_conn, first_boundary); write_data (l_conn, 'content-type', 'text/html; charset = gb2312 '); -- empty a row separately; otherwise, utl_smtp.write_data (l_conn, utl_tcp.crlf);/* sets the mail body to restore the separator to chr (10 ). This is mainly used to call this process in shell. If there are multiple rows, merge the contents of multiple rows into one row, and use l_splite to separate them and then replace chr (10) with l_crlf ). This step is required. Otherwise, you cannot send emails with multiple lines in the mail body */write_data (l_conn, '', replace (p_txt, l_splite, chr (10 )), chr (10), l_crlf), '',''); end_boundary (l_conn);/* close Data Writing */utl_smtp.close_data (l_conn ); /* close the connection */utl_smtp.quit (l_conn); end; begin the main process; begin in l_sendoraddress: = '<' | p_sendor | '>'; p_splite_str (p_receiver ); -- process the email address for k in 1 .. my_address_list.count loop p_email (l_sendoraddress, my_address_list (k); end loop;/* process email addresses, separate emails by commas */end;

Test the performance of PL/SQL:

Go to Outlook and check whether the email has arrived:

Haha! Receive the data in time. You can send related data at the underlying layer, hoping it will be useful to you!

In addition, I don't know if there is a function to send Mail directly at 11g ~

: Source code download ::