CREATE OR REPLACE PROCEDURE SCOTT. Html_email (p_to in VARCHAR2,--recipient address P_subject in VARCHAR2,--message subject p_html in VARCHAR2 DEFAULT NULL--message content supports HTML code) is L_boundary VARCHAR2 (255) DEFAULT ' A1B2C3D4E3F2G1 '; L_connection UTL_SMTP. CONNECTION; l_body_html CLOB:= Empty_clob; --This LOB would be is the EMAIL MESSAGE l_offset number; L_ammount number; L_temp VARCHAR2 (32767) DEFAULT NULL; L_account VARCHAR2 (100); L_password VARCHAR2 (100); P_from VARCHAR2 (200); P_text VARCHAR2 (200); P_smtp_hostname VARCHAR2 (200); P_smtp_portnum VARCHAR2 (200); BEGIN P_from:= ' [email protected] '; --Sender Address P_smtp_hostname:= ' smtp.163.com '; --SMTP Server P_smtp_portnum:= ' 25 '; --SMTP Server port number l_account:= ' [email protected] ';--Login Server with account L_password:= ' 123456 '; --Login Server password P_text:= ' There is no text '; L_connection:= Utl_smtp. Open_connection (p_smtp_hostname,25); Utl_smtp.ehlo (L_connection,p_smtp_hostname); --Greeting Server, note that this is used with EHLO, not using Helo--Enter user name password Utl_smtp.command (l_connection,' AUTH LOGIN '); Utl_smtp.command (L_connection, Utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw) ))); Utl_smtp.command (L_connection,utl_raw.cast_to_varchar2 (Utl_encode.base64_encode (Utl_raw.cast_to_raw (L_PASSWORD )))); Utl_smtp. MAIL (L_connection,' < ' | | p_from| | ' > '); --Sender Information Utl_smtp. RCPT (L_connection,' < ' | | p_to| | ' > '); --Recipient Address--Remember to add <> on both sides of the address, or you may get an error l_temp:= L_temp | | ' Mime-version:1.0 ' | | CHR (13) | | CHR (10); L_temp:= L_temp | | ' To: ' | | p_to | | CHR (13) | | CHR (10); L_temp:= L_temp | | ' From: ' | | P_from | | CHR (13) | | CHR (10); L_temp:= L_temp | | ' Subject: ' | | P_subject | | CHR (13) | | CHR (10); L_temp:= L_temp | | ' Reply-to: ' | | P_from | | CHR (13) | | CHR (10); L_temp:= L_temp | | ' Content-type:multipart/alternative; Boundary= ' | | CHR (34) | | l_boundary| | CHR (34) | | CHR (13) | | CHR (10); ------------------------------------------------------WRITE the HEADERS--Write header information Dbms_lob. Createtemporary (L_body_html,false,10); Dbms_lob. WRITE (L_body_html,length (l_temp),1, l_temp); -- ---------------------------------------------------- -- --WRITE the TEXT boundary--L_offset:=dbms_lob. GetLength (l_body_html) +1; --l_temp:= CHR (13) | | CHR (10) | | --' | | l_boundary| | CHR (13) | | CHR (10); --l_temp:= l_temp| | ' Content-type:text/plain;charset=us-ascii ' | | CHR (13) | | CHR (10) | | CHR (13) | | CHR (10); --Dbms_lob. WRITE (L_body_html,length (l_temp), l_offset,l_temp); -- ---------------------------------------------------- -- --WRITE the PLAIN TEXT portion of the EMAIL--L_offset: = Dbms_lob. GetLength (l_body_html) + 1; --Dbms_lob. WRITE (L_body_html,length (P_text), l_offset,p_text); ---------------------------------------------------- --WRITE the HTML boundary l_temp:= CHR (13) | | CHR (10) | | CHR (13) | | CHR (10) | | '---' | | l_boundary| | CHR (13) | | CHR (10); L_temp:= l_temp| | ' content-type:text/html; ' | | CHR (13) | | CHR (10) | | CHR (13) | | CHR (10); L_offset:= Dbms_lob. GetLength (l_body_html) + 1; Dbms_lob. WRITE (L_body_html,length (l_temp), l_offset,l_temp); ---------------------------------------------------- --WRITE The HTML portion of the MESSAGE l_offset:= Dbms_lob. GetLength (l_body_html) + 1; Dbms_lob. WRITE (L_body_html,length (p_html), l_offset,p_html); ---------------------------------------------------- --WRITE the Test Vale--L_temp: = ' I am Chinese '; --L_offset: = Dbms_lob. GetLength (l_body_html) + 1; --Dbms_lob. WRITE (L_body_html,length (l_temp), l_offset,l_temp); ---------------------------------------------------- --WRITE the FINAL HTML boundary--L_temp: =CHR (13) | | CHR (10) | | CHR (13) | | CHR (10) | | '---' | | l_boundary| | '---' | | CHR (13); --L_offset: =dbms_lob. GetLength (l_body_html) + 1; --Dbms_lob. WRITE (L_body_html,length (l_temp), l_offset,l_temp); ------------------------------------------------------SEND the EMAIL in 1900BYTE CHUNKS to utl_smtp l_offset:= 1; L_ammount:= 1900; Utl_smtp. Open_data (l_connection); While L_offset<Dbms_lob. GetLength (l_body_html) LOOP utl_smtp. Write_raw_data (L_connection,utl_raw.cast_to_raw (Dbms_lob. SUBSTR (L_body_html,l_ammount,l_offset)); L_offset:= l_offset+L_ammount; L_ammount:= LEAST (1900,dbms_lob. GetLength (l_body_html)-l_ammount); END LOOP; --Utl_smtp.write_raw_data (L_connection, Utl_raw.cast_to_raw (' I am Chinese '))); Utl_smtp. Close_data (l_connection); Utl_smtp. QUIT (l_connection); --Dbms_output.put_line (Dbms_lob. SUBSTR (L_body_html,dbms_lob. GetLength (l_body_html), 1)); Dbms_lob. Freetemporary (l_body_html); Dbms_output.put_line (To_char (Sysdate,' Yyyy-mm-dd hh24:mi:ss ') | | ' Send mail success!!! ‘); exception when others then Dbms_output.put_line (dbms_utility.format_error_stack); Dbms_output.put_line (Dbms_utility.format_call_stack); Dbms_output.put_line (SQLERRM); END; /
http://blog.csdn.net/blaider/article/details/5672729
Oracle 10G uses UTL_SMTP to send Chinese e-mails [Z]