oracle 发送邮件
来源：互联网 发布：java图形界面编程例子 编辑：程序博客网 时间：2024/06/05 11:09
发送 邮件公共包
CREATE OR REPLACE PACKAGE "SAD_SEND_MAIL_PKG" AUTHID CURRENT_USER AS /* $Header: CUXMAILS.pls 115.14.1159.2 2003/07/24 01:22:18 skkoppul ship $ */ -- Author : GW10451 -- Created : 2009-03-03 -- Purpose : To make MIME Header can display chinese and others language -- update WRITE_MIME_HEADER utl_smtp.write_data to utl_smtp.write_raw_data ----------------------- Customizable Section ----------------------- -- Customize the SMTP host, port and your domain name below. smtp_host VARCHAR2(256) := 'smtptest.huawei.com'; smtp_port PLS_INTEGER := 25; wallet_path VARCHAR2(256) := 'huawei.com'; -- Customize signature that will appear in the email's MIME header. -- Useful for versioning. -- MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Oracle 9i UTL_SMTP'; mailer_id CONSTANT VARCHAR2(256) := 'Oracle Contracts for Service'; -- A unique string that demarcates boundaries of parts in a multi-part -- email. The string should not appear inside the body of any part of the -- email. Customize this if needed or generate this randomly dynamically. boundary CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD'; crlf CONSTANT VARCHAR2(10) := chr(13) || chr(10); g_delimiter_value CONSTANT VARCHAR2(1) := ';'; --------------------- End Customizable Section --------------------- first_boundary CONSTANT VARCHAR2(256) := '--' || boundary || crlf; last_boundary CONSTANT VARCHAR2(256) := '--' || boundary || '--' || crlf; -- A MIME type that denotes multi-part email (MIME) messages. multipart_mime_type CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' || boundary || '"'; max_base64_line_width CONSTANT PLS_INTEGER := 76 / 4 * 3; normal_priority PLS_INTEGER := 3; TYPE recipient_rec IS RECORD ( mail_type VARCHAR2(10), to_email_address VARCHAR2(3000)); TYPE recipient_rec_tbl IS TABLE OF recipient_rec INDEX BY BINARY_INTEGER; ------------------------------------------------------------------------ ------------------------------------------------------------------------ FUNCTION get_address(pi_mailbox IN VARCHAR2) RETURN VARCHAR2; -- Mark a message-part boundary. Set <last> to TRUE for the last boundary. PROCEDURE write_boundary(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_LAST IN BOOLEAN DEFAULT FALSE); -- Write a MIME header PROCEDURE write_mime_header(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_NAME IN VARCHAR2, pi_VALUE IN VARCHAR2); -- Extended email API to send multiple emails in a session for better -- performance. First, begin an email session with begin_session. -- Then, begin each email with a session by calling begin_mail_in_session -- instead of begin_mail. End the email with end_mail_in_session instead -- of end_mail. End the email session by end_session. FUNCTION begin_session RETURN utl_smtp.connection; -- End an email session. PROCEDURE end_session(pio_conn IN OUT NOCOPY utl_smtp.connection); -- Begin an email in a session. PROCEDURE begin_mail_in_session(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT NULL); -- End an email in a session. PROCEDURE end_mail_in_session(pio_conn IN OUT NOCOPY utl_smtp.connection); -- Extended email API to send email in HTML or plain text with no size limit. -- First, begin the email by begin_mail(). Then, call write_text() repeatedly -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send -- email in non-ASCII or multi-byte character set. End the email with -- end_mail(). FUNCTION begin_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT NULL) RETURN utl_smtp.connection; -- End the email. PROCEDURE end_mail(pio_conn IN OUT NOCOPY utl_smtp.connection); -- A simple email API for sending email in plain text in a single call. PROCEDURE mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_message IN VARCHAR2); -- Write email body in ASCII PROCEDURE write_text(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_message IN VARCHAR2); -- Write email body in non-ASCII (including multi-byte). The email body -- will be sent in the database character set. PROCEDURE write_mb_text(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_message IN VARCHAR2); -- Write email body in binary PROCEDURE write_raw(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_message IN RAW); -- APIs to send email with attachments. Attachments are sent by sending -- emails in "multipart/mixed" MIME format. Specify that MIME format when -- beginning an email with begin_mail(). -- Send a single text attachment. PROCEDURE attach_text(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_data IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_inline IN BOOLEAN DEFAULT TRUE, pi_filename IN VARCHAR2 DEFAULT NULL, pi_LAST IN BOOLEAN DEFAULT FALSE); -- Send a binary attachment. The attachment will be encoded in Base-64 -- encoding format. PROCEDURE attach_base64(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_data IN RAW /*, pi_mime_type IN VARCHAR2 DEFAULT 'application/pdf', pi_inline IN BOOLEAN DEFAULT TRUE, pi_filename IN VARCHAR2 DEFAULT NULL, pi_LAST IN BOOLEAN DEFAULT FALSE*/); PROCEDURE attach_base64(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_document IN BLOB); PROCEDURE attach_base64(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_document IN CLOB); -- Send an attachment with no size limit. First, begin the attachment -- with begin_attachment(). Then, call write_text repeatedly to send -- the attachment piece-by-piece. If the attachment is text-based but -- in non-ASCII or multi-byte character set, use write_mb_text() instead. -- To send binary attachment, the binary content should first be -- encoded in Base-64 encoding format using the demo package for 8i, -- or the native one in 9i. End the attachment with end_attachment. PROCEDURE begin_attachment(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_inline IN BOOLEAN DEFAULT TRUE, pi_filename IN VARCHAR2 DEFAULT NULL, pi_transfer_enc IN VARCHAR2 DEFAULT NULL); -- End the attachment. PROCEDURE end_attachment(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_LAST IN BOOLEAN DEFAULT FALSE); -- This is the main program. It will call the other procedures to send the -- attachment over. PROCEDURE send_binary_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'application/pdf', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_path_name IN VARCHAR2, pi_file_name IN VARCHAR2); PROCEDURE send_text_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN VARCHAR2, pi_file_name IN VARCHAR2); PROCEDURE send_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN BLOB, pi_file_name IN VARCHAR2); PROCEDURE send_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN CLOB, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN CLOB, pi_file_name IN VARCHAR2); PROCEDURE send_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority); PROCEDURE send_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN OUT NOCOPY CLOB, pi_file_name IN VARCHAR2, pi_inline IN BOOLEAN); PROCEDURE send_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN OUT NOCOPY BLOB, pi_file_name IN VARCHAR2, pi_inline IN BOOLEAN);--发送文本邮件 无中文乱码PROCEDURE send_text_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority);------------------------------------------------------------- -- This Procedure takes a URL which yields a PDF or text pi_document and -- sends the retrieved pi_document as an attachment to the email. -------------------------------------------------------------- PROCEDURE send_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_url IN VARCHAR2, pi_file_name IN VARCHAR2); /*--------------------------------------------- --Name : get_mailers --Purpose : Get mailers --Author : kacm --Date : 2009-09-14 ---------------------------------------------*/ PROCEDURE get_mailers(pi_send_to IN VARCHAR2, po_recipient_rec_tbl OUT sad_send_mail_pkg.recipient_rec_tbl, po_message OUT VARCHAR2);END sad_send_mail_pkg; /CREATE OR REPLACE PACKAGE BODY "SAD_SEND_MAIL_PKG" AS /* $Header: CUXMAILB.pls 115.13.1159.2 2003/07/24 01:22:38 skkoppul ship $ */ -- Return the email address in the mailbox. The format of mailbox -- may be in one of these formats: -- someone@some-domain -- "Someone" <someone@some-domain> -- Someone <someone@some-domain> FUNCTION get_address(pi_mailbox IN VARCHAR2) RETURN VARCHAR2 IS i PLS_INTEGER; str VARCHAR2(256); BEGIN i := instr(pi_mailbox, '<', -1); IF (i > 0) THEN str := substr(pi_mailbox, i + 1); RETURN substr(str, 1, instr(str, '>') - 1); ELSE RETURN pi_mailbox; END IF; EXCEPTION WHEN OTHERS THEN /* log('get_address : ' || SQLERRM);*/ NULL; END get_address; -- Write a MIME header PROCEDURE write_mime_header(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_NAME IN VARCHAR2, pi_VALUE IN VARCHAR2) IS BEGIN --utl_smtp.write_data(pio_conn, name || ': ' || value || CRLF); --To make MIME Header can display chinese and others language --update WRITE_MIME_HEADER utl_smtp.write_data to utl_smtp.write_raw_data utl_smtp.write_raw_data(pio_conn, utl_raw.cast_to_raw(convert(pi_NAME || ': ' || pi_VALUE || crlf, 'UTF8'))); EXCEPTION WHEN OTHERS THEN --log('write_mime_header : ' || SQLERRM); NULL; END write_mime_header; -- Mark a message-part boundary. Set <last> to TRUE for the last boundary. PROCEDURE write_boundary(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_LAST IN BOOLEAN DEFAULT FALSE) IS BEGIN IF (pi_LAST) THEN utl_smtp.write_data(pio_conn, last_boundary); ELSE utl_smtp.write_data(pio_conn, first_boundary); END IF; EXCEPTION WHEN OTHERS THEN --log('write_boundary : ' || SQLERRM); NULL; END write_boundary; ------------------------------------------------------------------------ FUNCTION begin_session RETURN utl_smtp.connection IS pio_conn utl_smtp.connection; status utl_smtp.reply; BEGIN -- open SMTP connection status := utl_smtp.open_connection(smtp_host, smtp_port, pio_conn); -- Status code 220 - Service is ready IF (status.code <> 220) THEN --log(status.code || ': ' || status.text); NULL; END IF; status := utl_smtp.helo(pio_conn, smtp_host); -- @@ or EHLO? -- Status code 250 - Requested mail action OKAY completed -- Hand shaking working IF (status.code <> 250) THEN --log(status.code || ': ' || status.text); NULL; END IF; RETURN pio_conn; EXCEPTION WHEN OTHERS THEN --log('begin_session : ' || SQLERRM); NULL; END begin_session; ------------------------------------------------------------------------ PROCEDURE end_session(pio_conn IN OUT NOCOPY utl_smtp.connection) IS BEGIN utl_smtp.quit(pio_conn); EXCEPTION WHEN OTHERS THEN --log('end_session : ' || SQLERRM); NULL; END end_session; ------------------------------------------------------------------------ PROCEDURE begin_mail_in_session(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT NULL) IS l_count NUMBER := 1; BEGIN -- Specify pi_sender's address (our server allows bogus address -- as long as it is a full email address (xxx@yyy.com). utl_smtp.mail(pio_conn, get_address(pi_sender)); -- Specify recipient(s) of the email. WHILE l_count <= pi_recipient_tbl.COUNT LOOP utl_smtp.rcpt(pio_conn, get_address(pi_recipient_tbl(l_count).to_email_address)); l_count := l_count + 1; END LOOP; -- Start body of email utl_smtp.open_data(pio_conn); -- Set "From" MIME header write_mime_header(pio_conn, 'From', pi_sender); -- Set "To" MIME header l_count := 1; WHILE l_count <= pi_recipient_tbl.COUNT LOOP IF upper(nvl(pi_recipient_tbl(l_count).mail_type, 'TO')) = 'TO' THEN write_mime_header(pio_conn, 'To', pi_recipient_tbl(l_count).to_email_address); ELSIF upper(pi_recipient_tbl(l_count).mail_type) = 'CC' THEN write_mime_header(pio_conn, 'CC', pi_recipient_tbl(l_count).to_email_address); ELSIF upper(pi_recipient_tbl(l_count).mail_type) = 'BCC' THEN write_mime_header(pio_conn, 'Bcc', pi_recipient_tbl(l_count).to_email_address); ELSIF upper(pi_recipient_tbl(l_count).mail_type) = 'REPLY-TO' THEN write_mime_header(pio_conn, 'Reply-To', pi_recipient_tbl(l_count).to_email_address); END IF; l_count := l_count + 1; END LOOP; -- Set "Subject" MIME header write_mime_header(pio_conn, 'Subject', pi_subject); -- Set "Content-Type" MIME header write_mime_header(pio_conn, 'Content-Type', pi_mime_type); -- Set "X-Mailer" MIME header write_mime_header(pio_conn, 'X-Mailer', mailer_id); -- Set pi_priority: -- High Normal Low -- 1 2 3 4 5 IF (pi_priority IS NOT NULL) THEN write_mime_header(pio_conn, 'X-Priority', pi_priority); END IF; -- Send an empty line to denotes end of MIME headers and -- beginning of message body. utl_smtp.write_data(pio_conn, crlf); IF (pi_mime_type LIKE 'multipart/mixed%') THEN write_text(pio_conn, 'This is a multi-part message in MIME format.' || crlf); END IF; EXCEPTION WHEN OTHERS THEN --log('begin_mail_in_session : ' || SQLERRM); NULL; END begin_mail_in_session; ------------------------------------------------------------------------ PROCEDURE end_mail_in_session(pio_conn IN OUT NOCOPY utl_smtp.connection) IS BEGIN utl_smtp.close_data(pio_conn); EXCEPTION WHEN OTHERS THEN --log('end_mail_in_session : ' || SQLERRM); NULL; END end_mail_in_session; ------------------------------------------------------------------------ FUNCTION begin_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT NULL) RETURN utl_smtp.connection IS pio_conn utl_smtp.connection; BEGIN pio_conn := begin_session; begin_mail_in_session(pio_conn, pi_sender, pi_recipient_tbl, pi_subject, pi_mime_type, pi_priority); RETURN pio_conn; EXCEPTION WHEN OTHERS THEN --log('begin_mail : ' || SQLERRM); NULL; END begin_mail; ------------------------------------------------------------------------ PROCEDURE end_mail(pio_conn IN OUT NOCOPY utl_smtp.connection) IS BEGIN end_mail_in_session(pio_conn); end_session(pio_conn); EXCEPTION WHEN OTHERS THEN --log('end_mail : ' || SQLERRM); NULL; END end_mail; ------------------------------------------------------------------------ PROCEDURE mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_message IN VARCHAR2) IS pio_conn utl_smtp.connection; BEGIN pio_conn := begin_mail(pi_sender, pi_recipient_tbl, pi_subject); write_text(pio_conn, pi_message); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('mail : ' || SQLERRM); NULL; END mail; ------------------------------------------------------------------------ PROCEDURE write_text(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_message IN VARCHAR2) IS BEGIN utl_smtp.write_data(pio_conn, pi_message); EXCEPTION WHEN OTHERS THEN --log('write_text : ' || SQLERRM); NULL; END write_text; ------------------------------------------------------------------------ PROCEDURE write_mb_text(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_message IN VARCHAR2) IS BEGIN utl_smtp.write_raw_data(pio_conn, utl_raw.cast_to_raw(pi_message)); EXCEPTION WHEN OTHERS THEN --log('write_mb_text : ' || SQLERRM); NULL; END write_mb_text; ------------------------------------------------------------------------ PROCEDURE write_raw(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_message IN RAW) IS BEGIN utl_smtp.write_raw_data(pio_conn, pi_message); EXCEPTION WHEN OTHERS THEN --log('write_raw : ' || SQLERRM); NULL; END write_raw; ------------------------------------------------------------------------ PROCEDURE attach_text(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_data IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_inline IN BOOLEAN DEFAULT TRUE, pi_filename IN VARCHAR2 DEFAULT NULL, pi_LAST IN BOOLEAN DEFAULT FALSE) IS BEGIN begin_attachment(pio_conn, pi_mime_type, pi_inline, pi_filename); write_text(pio_conn, pi_data); end_attachment(pio_conn, pi_LAST); EXCEPTION WHEN OTHERS THEN --log('attach_text : ' || SQLERRM); NULL; END attach_text; ------------------------------------------------------------------------ PROCEDURE attach_base64(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_data IN RAW /*, pi_mime_type IN VARCHAR2 DEFAULT 'application/pdf', pi_inline IN BOOLEAN DEFAULT TRUE, pi_filename IN VARCHAR2 DEFAULT NULL, last IN BOOLEAN DEFAULT FALSE*/) IS i PLS_INTEGER; len PLS_INTEGER; BEGIN i := 1; len := utl_raw.length(pi_data); WHILE (i < len) LOOP IF (i + max_base64_line_width < len) THEN utl_smtp.write_raw_data(pio_conn, utl_encode.base64_encode(utl_raw.substr(pi_data, i, max_base64_line_width))); ELSE utl_smtp.write_raw_data(pio_conn, utl_encode.base64_encode(utl_raw.substr(pi_data, i))); END IF; utl_smtp.write_data(pio_conn, crlf); i := i + max_base64_line_width; END LOOP; EXCEPTION WHEN OTHERS THEN --log('attach_base64 : ' || SQLERRM); NULL; END attach_base64; PROCEDURE attach_base64(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_document IN BLOB) IS clob_length INTEGER; offset INTEGER; amount INTEGER; l_raw RAW(30000); l_max_length INTEGER := 30000; BEGIN clob_length := dbms_lob.getlength(pi_document); offset := 1; WHILE clob_length > 0 LOOP IF clob_length < l_max_length THEN amount := clob_length; ELSE amount := l_max_length; END IF; dbms_lob.READ(pi_document, amount, offset, l_raw); attach_base64(pio_conn, l_raw); clob_length := clob_length - l_max_length; offset := offset + l_max_length; END LOOP; END attach_base64; PROCEDURE attach_base64(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_document IN CLOB) IS clob_length INTEGER; offset INTEGER; amount INTEGER; l_str VARCHAR2(32767); l_raw RAW(32767); l_max_length INTEGER := 30000; BEGIN clob_length := dbms_lob.getlength(pi_document); offset := 1; WHILE clob_length > 0 LOOP IF clob_length < l_max_length THEN amount := clob_length; ELSE amount := l_max_length; END IF; dbms_lob.READ(pi_document, amount, offset, l_str); l_raw := utl_raw.cast_to_raw(l_str); attach_base64(pio_conn, l_raw); clob_length := clob_length - l_max_length; offset := offset + l_max_length; END LOOP; END attach_base64; ------------------------------------------------------------------------ PROCEDURE begin_attachment(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_inline IN BOOLEAN DEFAULT TRUE, pi_filename IN VARCHAR2 DEFAULT NULL, pi_transfer_enc IN VARCHAR2 DEFAULT NULL) IS BEGIN write_boundary(pio_conn); write_mime_header(pio_conn, 'Content-Type', pi_mime_type); IF (pi_filename IS NOT NULL) THEN IF (pi_inline) THEN write_mime_header(pio_conn, 'Content-Disposition', 'pi_inline; pi_filename="' || pi_filename || '"'); ELSE write_mime_header(pio_conn, 'Content-Disposition', 'attachment; pi_filename="' || pi_filename || '"'); END IF; END IF; IF (pi_transfer_enc IS NOT NULL) THEN write_mime_header(pio_conn, 'Content-Transfer-Encoding', pi_transfer_enc); END IF; utl_smtp.write_data(pio_conn, crlf); EXCEPTION WHEN OTHERS THEN --log('begin_attachment : ' || SQLERRM); NULL; END begin_attachment; ------------------------------------------------------------------------ PROCEDURE end_attachment(pio_conn IN OUT NOCOPY utl_smtp.connection, pi_LAST IN BOOLEAN DEFAULT FALSE) IS BEGIN utl_smtp.write_data(pio_conn, crlf); IF (pi_LAST) THEN write_boundary(pio_conn, pi_LAST); END IF; EXCEPTION WHEN OTHERS THEN --log('end_attachment : ' || SQLERRM); NULL; END end_attachment; ------------------------------------------------------------------------ PROCEDURE send_binary_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'application/pdf', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_path_name IN VARCHAR2, pi_file_name IN VARCHAR2) IS pio_conn utl_smtp.connection; l_file_loc BFILE; l_raw RAW(32000); l_num INTEGER; l_amount BINARY_INTEGER := max_base64_line_width * 10; -- 32000; --Modified by Allen_He@satyam 2009/03/04, 3200 is wrong l_offset INTEGER := 1; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); attach_text(pio_conn, pi_mail_text, 'text/html'); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => TRUE, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); ------------ It will upload the physical file ---------------- l_file_loc := bfilename(pi_path_name, pi_file_name); l_num := dbms_lob.getlength(l_file_loc); dbms_lob.OPEN(file_loc => l_file_loc, open_mode => dbms_lob.file_readonly); WHILE l_offset < l_num LOOP dbms_lob.READ(file_loc => l_file_loc, amount => l_amount, offset => l_offset, buffer => l_raw); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); l_offset := l_offset + l_amount; IF (l_offset + l_amount) > l_num THEN l_amount := l_num - l_offset; END IF; END LOOP; dbms_lob.fileclose(file_loc => l_file_loc); -------------------------------------------------------------- end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_binary_attachment : ' || SQLERRM); NULL; END send_binary_attachment; ------------------------------------------------------------------------ PROCEDURE send_text_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN VARCHAR2, pi_file_name IN VARCHAR2) IS pio_conn utl_smtp.connection; l_raw RAW(32767); BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); attach_text(pio_conn => pio_conn, pi_data => pi_mail_text, pi_mime_type => pi_mime_type); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => TRUE, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); l_raw := utl_raw.cast_to_raw(pi_document); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_text_attachment : ' || SQLERRM); NULL; END send_text_attachment; ------------------------------------------------------------------------ PROCEDURE send_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN BLOB, pi_file_name IN VARCHAR2) IS pio_conn utl_smtp.connection; l_raw RAW(32767); l_str VARCHAR2(32767); clob_length INTEGER; offset INTEGER; amount INTEGER; l_amount NUMBER; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); attach_text(pio_conn => pio_conn, pi_data => pi_mail_text, pi_mime_type => pi_mime_type); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => FALSE, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); ------ It will upload the physical file ---------------- clob_length := dbms_lob.getlength(pi_document); offset := 1; l_amount := 30000; WHILE clob_length > 0 LOOP IF clob_length < l_amount THEN amount := clob_length; ELSE amount := l_amount; END IF; dbms_lob.READ(pi_document, amount, offset, l_str); --l_raw := UTL_RAW.CAST_TO_RAW(l_str); l_raw := l_raw || l_str; --IF mod(utl_raw.length(l_raw),3) = 0 THEN attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); l_str := NULL; l_raw := NULL; --END IF; clob_length := clob_length - l_amount; offset := offset + l_amount; END LOOP; -------------------------------------------------------- end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_attachment : ' || SQLERRM); NULL; END send_attachment; PROCEDURE send_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN CLOB, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN CLOB, pi_file_name IN VARCHAR2) IS pio_conn utl_smtp.connection; l_raw RAW(3000); l_str VARCHAR2(3000); clob_length INTEGER; offset INTEGER; amount INTEGER; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => TRUE, pi_filename => 'content.html', pi_transfer_enc => 'base64'); ------ Message as a CLOB too ---------------- clob_length := dbms_lob.getlength(pi_mail_text); offset := 1; WHILE clob_length > 0 LOOP IF clob_length < 3000 THEN amount := clob_length; ELSE amount := 3000; END IF; dbms_lob.READ(pi_mail_text, amount, offset, l_str); l_raw := utl_raw.cast_to_raw(l_str); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, 'content.html', FALSE*/); clob_length := clob_length - 3000; offset := offset + 3000; END LOOP; -------------------------------------------------------- end_attachment(pio_conn); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => FALSE, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); ------ It will upload the physical file ---------------- clob_length := dbms_lob.getlength(pi_document); offset := 1; WHILE clob_length > 0 LOOP IF clob_length < 3000 THEN amount := clob_length; ELSE amount := 3000; END IF; dbms_lob.READ(pi_document, amount, offset, l_str); l_raw := utl_raw.cast_to_raw(l_str); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); clob_length := clob_length - 3000; offset := offset + 3000; END LOOP; -------------------------------------------------------- end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_attachment : ' || SQLERRM); NULL; END send_attachment; PROCEDURE send_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority) IS pio_conn utl_smtp.connection; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); attach_text(pio_conn => pio_conn, pi_data => pi_mail_text, pi_mime_type => pi_mime_type); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_mail : ' || SQLERRM); NULL; END send_mail; PROCEDURE send_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN OUT NOCOPY CLOB, pi_file_name IN VARCHAR2, pi_inline IN BOOLEAN) IS pio_conn utl_smtp.connection; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => pi_inline, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); attach_base64(pio_conn, pi_document); end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_attachment : ' || SQLERRM); NULL; END send_mail; PROCEDURE send_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_document IN OUT NOCOPY BLOB, pi_file_name IN VARCHAR2, pi_inline IN BOOLEAN) IS pio_conn utl_smtp.connection; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => pi_inline, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); attach_base64(pio_conn, pi_document); end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_attachment : ' || SQLERRM); NULL; END send_mail; PROCEDURE send_text_mail(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority) IS pio_conn utl_smtp.connection; BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); attach_text(pio_conn => pio_conn, pi_data => pi_mail_text, pi_mime_type => pi_mime_type); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_mail : ' || SQLERRM); NULL; END send_text_mail;/**/ -------------------------------------------------------------- -- This Procedure takes a URL which yields a PDF or text pi_document and -- sends the retrieved pi_document as an attachment to the email. -------------------------------------------------------------- PROCEDURE send_attachment(pi_sender IN VARCHAR2, pi_recipient_tbl IN recipient_rec_tbl, pi_subject IN VARCHAR2, pi_mail_text IN VARCHAR2, pi_mime_type IN VARCHAR2 DEFAULT 'text/plain', pi_priority IN PLS_INTEGER DEFAULT normal_priority, pi_url IN VARCHAR2, pi_file_name IN VARCHAR2) IS pio_conn utl_smtp.connection; l_raw RAW(32767); pieces utl_http.html_pieces; wallet_pswd VARCHAR2(2000); l_str VARCHAR2(32767); BEGIN pio_conn := begin_mail(pi_sender => pi_sender, pi_recipient_tbl => pi_recipient_tbl, pi_subject => pi_subject, pi_mime_type => multipart_mime_type, pi_priority => pi_priority); attach_text(pio_conn, pi_mail_text || 'test1'); begin_attachment(pio_conn => pio_conn, pi_mime_type => pi_mime_type, pi_inline => FALSE, pi_filename => pi_file_name, pi_transfer_enc => 'base64'); IF (upper(substr(pi_url, 1, 5)) = 'HTTPS') THEN IF length(wallet_path) > 0 THEN IF instr(wallet_path, '$$') > 0 THEN wallet_pswd := substr(wallet_path, instr(wallet_path, '$$') + 2); ELSE wallet_pswd := NULL; END IF; pieces := utl_http.request_pieces(url => pi_url, max_pieces => 32767, proxy => NULL, wallet_path => wallet_path, wallet_password => wallet_pswd); ELSE /*fnd_message.set_name('OKS', 'OKS_INVALID_WALLET_PATH'); log(fnd_message.get);*/ NULL; END IF; ELSE pieces := utl_http.request_pieces(url => pi_url, max_pieces => 32767, proxy => NULL, wallet_path => NULL, wallet_password => NULL); END IF; FOR i IN 1 .. pieces.COUNT LOOP l_str := l_str || pieces(i); IF lengthb(l_str) > 30000 AND i <> pieces.COUNT THEN l_raw := utl_raw.cast_to_raw(substrb(l_str, 1, 30000)); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); l_str := substrb(l_str, 30001); l_raw := NULL; ELSIF lengthb(l_str) > 30000 AND i = pieces.COUNT THEN l_raw := utl_raw.cast_to_raw(substrb(l_str, 1, 30000)); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); l_str := substrb(l_str, 30001); l_raw := utl_raw.cast_to_raw(l_str); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); l_str := NULL; l_raw := NULL; ELSIF lengthb(l_str) <= 30000 AND i = pieces.COUNT THEN l_raw := utl_raw.cast_to_raw(l_str); attach_base64(pio_conn, l_raw /*, pi_mime_type, TRUE, pi_file_name, FALSE*/); l_str := NULL; l_raw := NULL; /* ELSE IF MOD(lengthb(l_str),3) = 0 AND i <> pieces.COUNT THEN l_raw := UTL_RAW.CAST_TO_RAW(l_str); attach_base64(pio_conn, l_raw\*, pi_mime_type, TRUE, pi_file_name, FALSE*\); l_str := NULL; l_raw := NULL; ELSIF i = pieces.COUNT THEN l_raw := UTL_RAW.CAST_TO_RAW(l_str); attach_base64(pio_conn, l_raw\*, pi_mime_type, TRUE, pi_file_name, FALSE*\); l_str := NULL; l_raw := NULL; END IF;*/ END IF; END LOOP; -------------------------------------------------------- end_attachment(pio_conn); end_mail(pio_conn); EXCEPTION WHEN OTHERS THEN --log('send_attachment : ' || SQLERRM); NULL; END send_attachment;------------------------------------------------------------------------ /*--------------------------------------------- --Name : get_mailers --Purpose : Get mailers --Author : kacm --Date : 2009-09-14 ---------------------------------------------*/ PROCEDURE get_mailers(pi_send_to IN VARCHAR2, po_recipient_rec_tbl OUT sad_send_mail_pkg.recipient_rec_tbl, po_message OUT VARCHAR2) IS l_string VARCHAR2(2000); -- l_w3_account tpl_user_t.w3_account%TYPE; update by HGW10451 2009.10.23 l_email_address VARCHAR2(200) /*tpl_user_t.email%TYPE*/ ; --update by HGW10451 2009.10.23 l_split_symbol VARCHAR2(10) := ','; l_split_symbol_position NUMBER; l_index NUMBER; BEGIN l_string := pi_send_to; WHILE l_string IS NOT NULL LOOP l_split_symbol_position := instr(l_string, l_split_symbol); IF l_split_symbol_position > 0 THEN l_email_address := TRIM(substr(l_string, 1, l_split_symbol_position - 1)); l_string := substr(l_string, l_split_symbol_position + 1); ELSE l_email_address := TRIM(l_string); l_string := NULL; END IF; l_index := po_recipient_rec_tbl.COUNT + 1; po_recipient_rec_tbl(l_index).mail_type := 'TO'; po_recipient_rec_tbl(l_index).to_email_address := l_email_address; END LOOP; EXCEPTION WHEN OTHERS THEN po_message := '[get_mailers]' || substr(SQLERRM, 1, 80); END get_mailers;END sad_send_mail_pkg;/测试demo
DECLARE -- Local variables here g_sender CONSTANT VARCHAR2(100) := 'public_wfmail@notesmail.xxx.com'; g_mime_type CONSTANT VARCHAR2(10) := 'text/html'; g_priority CONSTANT PLS_INTEGER := sad_send_mail_pkg.normal_priority; g_inline CONSTANT BOOLEAN := FALSE; l_recipient_tbl sad_send_mail_pkg.recipient_rec_tbl; l_subject VARCHAR2(100) := 'test subject'; l_send_to VARCHAR2(100) := '123456@notesmail.xxx.com'; l_copy_to VARCHAR2(100); l_return_msg VARCHAR2(100); v_document CLOB; v_msg VARCHAR2(3000); l_line_msg VARCHAR2(100); v_line_msg VARCHAR2(100);v_str1 VARCHAR2(3000);v_url VARCHAR2(1000):='http://dggtsv047-lx.huawei.com/ras/sad/contractQuery.do?method=';BEGIN v_msg := '<html><body><div style="border-bottom:3px solid #d9d9d9; repeat-x 0 1px;"><div style="border:1px solid #c8cfda; no-repeat right top; padding:40px;"><p>Dear Master</p>' || '<table width="60%" height="20%" border="0" align="center">' || '<tr>' || '<td ><H3>The following contract has completed order splitting by the system and been published. If there is any problem, contact the regional service order splitting coordinator. To view the details about the contract, please click the contract No.</H3></td></tr></table></br>' || '<table width="45%" border="1" align="center">' || ' <tr>' || '<td align="center" valign="middle">Contract NO</td>' || '<td align="center" valign="middle">Version</td>' || '<td align="center" valign="middle">Service solution manager</td>' || '<td align="center" valign="middle">Contract Order Status</td>' || '</tr>'; --转换收件人地址 sad_send_mail_pkg.get_mailers(l_send_to, l_copy_to, l_recipient_tbl , v_line_msg); --生成邮件内容 dbms_lob.createtemporary(v_document, TRUE); dbms_lob.open(v_document, dbms_lob.lob_readwrite); --添加邮件头 dbms_lob.writeappend(lob_loc => v_document, amount => length(v_msg), buffer => v_msg); --添加合同内容 v_str1 :='test'; dbms_lob.writeappend(lob_loc => v_document, amount => length(v_str1), buffer => v_str1); /* v_msg:=NULL; v_msg := '<tr>' || '<td align="center" valign="middle"><a href="' ||v_url || '">aaaaaaaaaaaa' || i|| '</a>aaaaaaaaaaaaaaaaaa</td>' || '<td align="center" valign="middle">bbbbbbbbbbbbbbbb' ||i|| '</td>' || '<td align="center" valign="middle">cccccccccccccccccc' ||i|| '</td>' || '<td align="center" valign="middle">ddddddddddddd'||i|| '</td></tr>'; dbms_lob.writeappend(lob_loc => v_document, amount => length(v_msg), buffer => v_msg); */ --添加邮件结束信息 v_msg := '</table></div></div></body></html>'; dbms_lob.writeappend(lob_loc => v_document, amount => length(v_str1), buffer => v_str1); /* --拆分最大允差超限，生成邮件内容 IF g_over_variance_list.count > 0 THEN v_line_msg := lpad('RAS Decimal difference exceeds', 70) || chr(13) || chr(10) ||chr(13) || chr(10); v_line_msg := v_line_msg || 'Contract Number Version Signed Org ' || ' Currency Product Line Or Code Message' || chr(13) || chr(10); v_line_msg := v_line_msg || '-------------------- ---------- ---------------------------' || ' ------------- -------------------------------------------------------------' || chr(13) || chr(10); dbms_lob.writeappend(lob_loc => v_document, amount => length(v_line_msg), buffer => v_line_msg); FOR i IN 1 .. g_over_variance_list.count LOOP v_line_msg := g_over_variance_list(i) || chr(13) || chr(10); dbms_lob.writeappend(lob_loc => v_document, amount => length(v_line_msg), buffer => v_line_msg); END LOOP; v_line_msg := chr(13) || chr(10); dbms_lob.writeappend(lob_loc => v_document, amount => length(v_line_msg), buffer => v_line_msg); END IF;*/ dbms_lob.close(v_document); /*sad_send_mail_pkg.send_mail(pi_sender => g_sender, pi_recipient_tbl => l_recipient_tbl, pi_subject => l_subject, pi_mail_text => v_msg, pi_mime_type =>g_mime_type, pi_priority =>g_priority );*/sad_send_mail_pkg.send_mail(pi_sender => g_sender, pi_recipient_tbl => l_recipient_tbl, pi_subject => l_subject, pi_mime_type =>g_mime_type, pi_priority =>g_priority, pi_document =>v_document, pi_file_name =>NULL, pi_inline =>g_inline);END; 0 0
- Oracle发送HTML邮件
- 用oracle发送邮件
- 用oracle发送邮件
- 使用Oracle发送邮件
- 用oracle发送邮件
- Oracle 发送邮件
- oracle发送邮件
- Oracle发送邮件
- oracle 发送邮件
- 用oracle发送邮件
- oracle 发送邮件
- ORACLE 邮件发送
- Oracle UTL_SMTP邮件发送
- 利用Oracle发送邮件
- Oracle 实现邮件发送
- oracle 服务器发送邮件
- oracle发送邮件
- oracle发送邮件的过程
- Java日志 - hibernate中为什么要额外配置slf4j
- TeeChart for .NET常用属性总结
- VIM操作多行缩进
- Android 设置飞行模式，判断是否是飞行模式
- c3p0数据库连接池实例
- oracle 发送邮件
- MySQL常用内置函数说明+concat+insert(str,m,n,inser_str)+week(now())+
- 微软图形控件（Chart或MSchart）的用法
- MySQL主从服务器配置的优势
- 安装CentOS 6.4 后无法上网
- 连载：面向对象葵花宝典：思想、技巧与实践（11） - “封装” 详解
- Linux系统信息查看命令大全(查看内存使用情况)
- opencv资料和文档
- C# 无边框窗体移动 点击任务栏实现最小化与还原
