Stored Procedure Code of Oracle mail sending

The SQL code is as follows:

 

Create or replace procedure procsendemail (p_txt varchar2, p_sub varchar2, p_sendor varchar2, p_receiver varchar2, p_server varchar2, p_port number default 25, p_need_smtp int default 0, p_user varchar2 default null, p_pass varchar2 default null, p_filename varchar2 default null, p_encode varchar2 default 'bit 7') authid CURRENT_USER is/*: use Oracle to send mail. Main functions: 1. Support for multiple recipients. 2. Support for Chinese Characters 3. CC users 4. Support for attachments larger than 32 K 5. Support for multi-line bodies 6. Support for multiple attachments 7. Support for text attachments and binary attachments 8. Support for HTML Format 8. Supported: suk parameter description: p_txt: Mail body p_sub: Mail title p_sendoraddress: sender email address p_receiveraddress: Receiving address, which can be sent to multiple addresses at the same time. "," or "is used between addresses "; "Separate p_emailserver: mail server address, which can be a domain name or IP p_port: mail server port p_need_smtp: whether SMTP authentication is required. 0 indicates no, and 1 indicates p_user: user name p_pass required for SMTP verification: Password p_filename required for SMTP authentication: attachment name, must contain the complete path, such as "D:/temp/a.txt ". There can be multiple attachments. The attachment names can only be separated by commas (,) or semicolons (;). p_encode: The attachment encoding conversion format, p_encode = 'bit 7' indicates the attachment of the text type p_encode = 'base64' indicates the attachment of the binary type. Note: 1. attachments of the text type cannot be sent in base64 format, otherwise Error 2. Multiple attachments can only be sent in the same format */l_crlf varchar2 (2): = utl_tcp.crlf; l_sendoraddress varchar2 (4000); l_splite varchar2 (10 ): = '++'; boundary constant varchar2 (256): = '----- bysuk'; first_boundary constant varchar2 (256): = '--' | boundary | l_crlf; last_boundary constant VaR Char2 (256): = '--' | boundary | '--' | l_crlf; multipart_mime_type constant varchar2 (256): = 'multipart/mixed; boundary = "'| boundary |'" ';/* the variables used to send large binary attachments */l_fil bfile; l_file_len number; l_modulo number; l_pieces number; l_file_handle utl_file.file_type; l_amt binary_integer: = 672*3;/* ensures proper format; 2016 */l_filepos pls_integer: = 1;/* pointer for the file */l_chun KS number; l_buf raw (2100); l_data raw (2100); l_max_line_width number: = 54; l_directory_base_name varchar2 (100): = 'dir _ for_send_mail '; l_line varchar2 (1000 ); rochelle mesg varchar2 (32767);/* The above part is the variable used to send large binary attachments */type address_list is table of varchar2 (100) index by binary_integer; my_address_list address_list; type acct_list is table of varchar2 (100) index by binary_integer; my_acct_list acct_list ;- ------------------------------------ Return the directory or name of the attachment source file ------------------------------------ function get_file (p_file varchar2, p_get INT) return varchar2 is -- p_get = 1 indicates the returned directory -- p_get = 2 indicates the returned file name Rochelle file varchar2 (1000); begin if instr (p_file, '/')> 0 then -- Windows if p_get = 1 then l_file: = substr (p_file, 1, instr (p_file, '/',-1)-1); elsif p_get = 2 then l_file: = substr (p_file,-(length (p_file )- Instr (p_file, '/',-1); end if; elsif instr (p_file, '/')> 0 then -- Linux/Unix if p_get = 1 then l_file: = substr (p_file, 1, instr (p_file, '/',-1)-1); elsif p_get = 2 then l_file: = substr (p_file,-(length (p_file) -instr (p_file, '/',-1); end if; return l_file; end; Delete directory -------------------------------------- procedure drop_directo Ry (p_directory_name varchar2) is begin execute immediate 'drop directory' | p_directory_name; Exception when others then NULL; end; Creating directory export procedure create_directory (p_directory_name varchar2, p_dir varchar2) is begin execute immediate 'create directory' | p_directory_name | 'as ''' | p_dir |'' ''; Execute immediate 'Grant read, write on directory' | p_directory_name | 'to public'; Exception when others then raise; end; segment the email address or attachment address 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 received email addresses. Table, including removing spaces, 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 substr (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; elsif p_splite_flag = 2 then -- table Processing attachment name my_acct_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; elsif p_splite_flag = 2 then my_acct_list (j): = l_addr; end if; end loop; end; begin write the mail header and content into procedure write_dat A (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/* begin is very important for Solving Chinese garbled characters */Merge (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 rows (Conn, rows); If (last) Then rows (Conn, last_boundary); end if; end; ---------------------------------------------- send attachments -------------------------------------------------- procedure attachment (Conn in out nocopy utl_smtp.connection, Mim E_type in varchar2 default 'text/plain ', inline in Boolean default true, filename in varchar2 default 't.txt', transfer_enc in varchar2 default '7 bit ', dt_name in varchar2 default '0 ') is l_filename varchar2 (1000); begin -- write the attachment header utl_smtp.write_data (Conn, first_boundary); -- set the attachment format write_data (Conn, 'content-type', mime_type ); -- if the file name is not empty, it indicates that there is an attachment drop_directory (dt_name); -- create directory create_directory (d T_name, get_file (filename, 1); -- Obtain the name of the attachment file Rochelle filename: = get_file (filename, 2); If (Inline) Then write_data (Conn, 'content-disposition ', 'inline; filename = "'| l_filename |'" '); else write_data (Conn, 'content-disposition', 'attachment; filename = "'| l_filename |'" '); end if; -- sets the conversion format of the attachment if (transfer_enc is not null) Then write_data (Conn, 'content-transfer-encoding ', transfer_enc); end if; UT L_smtp.write_data (Conn, utl_tcp.crlf); -- begin post attachment content if transfer_enc = 'bit 7' then -- if it is a text-type attachment begin l_file_handle: = utl_file.fopen, 'R'); -- open the file -- split the attachment into multiple parts, so that the attachment loop utl_file.get_line (l_file_handle, l_line) that exceeds 32 K can be sent; l_mesg: = l_line | l_crlf; write_data (Conn, '', l_mesg,'', ''); End loop; utl_file.fclose (l_file_handle); end_boundary (conn); Exception when others then utl _ File. fclose (l_file_handle); end_boundary (conn); NULL; end; -- end processing of text type attachments elsif transfer_enc = 'base64' then -- if it is a binary type attachment begin -- divide the attachment into multiple parts, so that you can send more than 32 K attachments l_filepos: = 1; -- reset offset. When sending multiple attachments, you must reset l_fil: = bfilename (dt_name, l_filename); l_file_len: = dbms_lob.getlength (l_fil); l_modulo: = Mod (l_file_len, l_amt); l_pieces: = trunc (l_file_len/l_amt); If (l_modulo <> 0) Then l_pieces: = l_pieces + 1; End if; dbms_lob.fileopen (l_fil, dbms_lob.file_readonly); dbms_lob.read (l_fil, l_amt, l_filepos, l_buf); l_data: = NULL; for I in 1 .. l_pieces loop l_filepos: = I * l_amt + 1; l_file_len: = l_file_len-l_amt; l_data: = utl_raw.concat (l_data, l_buf); l_chunks: = trunc (utl_raw.length (l_data) /l_max_line_width); if (I <> l_pieces) Then l_chunks: = l_chunks-1; end if; utl_smtp.write_raw_data (Conn, Round (l_data); l_data: = NULL; If (l_file_len <l_amt and l_file_len> 0) Then l_amt: = l_file_len; end if; dbms_lob.read (l_fil, l_amt, l_filepos, l_buf); End loop; dbms_lob.fileclose (l_fil); end_boundary (conn); Exception when others then dbms_lob.fileclose (l_fil); end_boundary (conn); raise; end; -- end processing binary attachment end if; -- end processing attachment content drop_directory (dt_name); end; -- End Process attachment -- When sending an email, initialize 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 email server information, connect to the email server */l_conn: = utl_smtp.open_connection (p_server, p_port); utl_smtp.helo (l_conn, p_server);/* SMTP server logon verification */If p_need_smtp = 1 then utl_smtp.command _ Conn, 'auth login', ''); utl_smtp.command (l_conn, encode (utl_encode.base64_encode (utl_raw.cast_to_raw (p_user); utl_smtp.command (l_conn, begin (begin (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 */utl_smtp.open_data (l_conn); write_data (l_co Nn, 'date', to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss');/* set the sender */write_data (l_conn, 'from ', p_sendor);/* Set recipient */write_data (l_conn, 'to', p_0000er);/* Set Email Subject */write_data (l_conn, 'subobject', p_sub ); write_data (l_conn, 'content-type', multipart_mime_type); values (l_conn, utl_tcp.crlf); values (l_conn, first_boundary); write_data (l_conn, 'content-type ', 'text/plain; charset = Gb2312 '); -- an empty line. Otherwise, the text does not display 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); -- if the file name is not empty, send the attachment if (p_filename is not null) then -- split the attachment address p_splite_str (p_filename, 2) According to the comma or semicolon; -- send the attachment cyclically (in the same email) for K in 1 .. export loop attachment (conn => l_conn, filename => my_acct_list (K), transfer_enc => p_encode, dt_name => l_directory_base_name | to_char (k); End loop; end if; /* close Data Writing */utl_smtp.close_data (l_conn);/* close the connection */utl_smtp.quit (l_conn);/* handle exceptions */exception when others then NULL; raise; end; ----------------------------------------------- main process begin in l_sendoraddress: = '<' | p_sendor | '>'; p_splite_str (p_receiver); -- process 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 */exception when others then raise; end;

Example:

Exec procsendemail ('hello', 'Hello test Oracle e-mail ', 'tianlesoftware @ gmail.com', 'tianlesoftware @ gmail.com ', 'mail server address', 'user ', 'pwd', '', 'bit 7 ');

---------------------------------------------------------------------------------
Email: tianlesoftware@gmail.com
QQ: tianlesoftware@gmail.com
Skype: tianlesoftware
Blog: http://www.tianlesoftware.com
WEAVER: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
LinkedIn: http://cn.linkedin.com/in/tianlesoftware

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----
Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)
Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823
Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940