发送 邮件公共包
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;