[ZT] Oracle mail storage process

 

Http://space.itpub.net/9399028/viewspace-687358

 

 

A usage for getting down from the Internet OracleSent Email Stored Procedure. You can directly call this stored procedure by sending an email. The parameter meanings are described. The Code is as follows: Create or replace procedure procsendemail, 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: Email ServerAddress, 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 the user name p_pass required for p_user: SMTP authentication: SMTP verification requires the password p_filename: attachment name, which must contain the complete path, for example, "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; example: exec procsendemail ('helloworld', 'Hello test Oracle e-mail ', 'xz @ Sina.com', 'xz @ Sina.com ', 'email server address', 'user ', 'pwd', '', 'bit 7'); Note: 1. if the email server address is incorrect, an error similar to this may occur:

Row 3 has an error:
ORA-29278: SMTP temporary error: 421 service not available
ORA-06512: In "SYS. utl_smtp", line 21
ORA-06512: In "SYS. utl_smtp", line 97
ORA-06512: In "SYS. utl_smtp", line 139
ORA-06512: In line 4

2. If the user name or password is incorrect, an error is reported:

Row 3 has an error:
ORA-29279: SMTP permanent error: 535 authorization failed (#5.7.0)
ORA-06512: In "SYS. utl_smtp", line 21
ORA-06512: In "SYS. utl_smtp", line 99
ORA-06512: In "SYS. utl_smtp", line 159
ORA-06512: in line 8

3. If you do not encode the user name and password, you will encounter the following error:

Row 3 has an error:
ORA-29279: SMTP permanent error: 501 malformed auth input (#5.5.4)
ORA-06512: In "SYS. utl_smtp", line 21
ORA-06512: In "SYS. utl_smtp", line 99
ORA-06512: In "SYS. utl_smtp", line 159
ORA-06512: in line 7

4. If you have not set a user name or password, or use the utl_mail package to connect to the email server that requires authorization verification, the following error will be reported:

Row 3 has an error:
ORA-29279: SMTP permanent error: 554 auth login first
ORA-06512: In "SYS. utl_smtp", line 21
ORA-06512: In "SYS. utl_smtp", line 99
ORA-06512: In "SYS. utl_smtp", line 241
ORA-06512: In "SYS. utl_mail", line 424
ORA-06512: In "SYS. utl_mail", line 594
ORA-06512: In line 2