发邮件封装包

DECLARE
-- Non-scalar parameters require additional processing
p_files cux_send_attachment_common.tab_of_attachments;

l_count NUMBER := 0;
BEGIN
FOR rec IN (SELECT v.file_name
,v.file_data
FROM fnd_lobs v
WHERE v.file_id IN (371009,
370609))
LOOP
l_count := l_count + 1;
p_files(l_count).attach_filename := rec.file_name;
p_files(l_count).attach_blob := rec.file_data;
END LOOP;

dbms_output.put_line(p_files.count);

dbms_output.put_line(p_files(1).attach_filename);
dbms_output.put_line(p_files(2).attach_filename);

cux_send_attachment_common.send_mail_files(p_to => 'XX@longcheer.com',
p_subject => '测试',
p_content => '测试',
p_files => p_files);
END;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE cux_send_attachment_common AS
/*==================================================
Program Name:
cux_send_attachment_common
Description:
This program provide concurrent main procedure to perform:
==================================================*/
g_smtp_host VARCHAR2(30) := '172.16.9.238';
g_smtp_port NUMBER := 587;
g_from VARCHAR2(30) := 'ebsadmin@longcheer.com';

TYPE attachment IS RECORD(
attach_filename VARCHAR2(240)
,attach_mime VARCHAR2(100)
,attach_blob BLOB);
TYPE tab_of_attachments IS TABLE OF attachment INDEX BY BINARY_INTEGER;

boundary CONSTANT VARCHAR2(256) := '--DMW.Boundary.605592468--';--'__7D81B75CCC90D2974F7A1CBD__';
first_boundary CONSTANT VARCHAR2(256) := '--' || boundary /*|| utl_tcp.crlf*/;
last_boundary CONSTANT VARCHAR2(256) := '--' || boundary || '--' /*|| utl_tcp.crlf*/;
multipart_mime_type CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="' || boundary || '"';

PROCEDURE send_mail_files(p_smtp_host IN VARCHAR2 DEFAULT g_smtp_host
,p_smtp_port IN NUMBER DEFAULT g_smtp_port--25
,p_from IN VARCHAR2 DEFAULT g_from
,p_to IN VARCHAR2
,p_cc IN VARCHAR2 DEFAULT NULL
,p_bcc IN VARCHAR2 DEFAULT NULL
,p_subject IN VARCHAR2
,p_content IN CLOB DEFAULT NULL
,p_content_type IN VARCHAR2 DEFAULT 'text/html; charset=UTF-8'
,p_files IN cux_send_attachment_common.tab_of_attachments);

END cux_send_attachment_common;

----------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY cux_send_attachment_common AS
/*==================================================
Program Name:
cux_send_attachment_common
Description:
This program provide concurrent main procedure to perform:
==================================================*/

-- Global variable
g_pkg_name CONSTANT VARCHAR2(30) := 'cux_send_attachment_common';
g_request_id NUMBER := fnd_global.conc_request_id;
-- Debug Enabled
l_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),
'N');

PROCEDURE log(p_msg_data IN VARCHAR2) IS
BEGIN
IF g_request_id <> -1 THEN
hand_conc_utl.log_msg(g_pkg_name || ':' || ' [' || to_char(SYSDATE,
'YYYY-MM-DD HH24:MI:SS') || '] ');
hand_conc_utl.log_msg(p_msg_data);
ELSE
dbms_output.put_line(g_pkg_name || ':' || ' [' || to_char(SYSDATE,
'YYYY-MM-DD HH24:MI:SS') || '] ');
dbms_output.put_line(p_msg_data);
END IF;
END;

FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
addr VARCHAR2(256);
i PLS_INTEGER;

FUNCTION lookup_unquoted_char(str IN VARCHAR2
,chrs IN VARCHAR2) RETURN PLS_INTEGER AS
c VARCHAR2(5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;

BEGIN
inside_quote := FALSE;
i := 1;
len := length(str);

WHILE (i <= len)
LOOP
c := substr(str,
i,
1);

IF (inside_quote) THEN
IF (c = '"') THEN
inside_quote := FALSE;
ELSIF (c = '\') THEN
i := i + 1; -- Skip the quote character
END IF;
END IF;

IF (c = '"') THEN
inside_quote := TRUE;
END IF;

IF (instr(chrs,
c) >= 1) THEN
RETURN i;
END IF;

i := i + 1;
END LOOP;
RETURN 0;
END;

BEGIN
addr_list := ltrim(addr_list);
i := lookup_unquoted_char(addr_list,
',;');

IF (i >= 1) THEN
addr := substr(addr_list,
1,
i - 1);
addr_list := substr(addr_list,
i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char(addr,
'<');

IF (i >= 1) THEN
addr := substr(addr,
i + 1);
i := instr(addr,
'>');

IF (i >= 1) THEN
addr := substr(addr,
1,
i - 1);
END IF;
END IF;
RETURN addr;
END;

PROCEDURE write_text(p_conn IN OUT NOCOPY utl_smtp.connection
,p_message IN VARCHAR2) IS
BEGIN
/*utl_smtp.write_data(p_conn,
p_message);*/
utl_smtp.write_raw_data(p_conn,
utl_raw.cast_to_raw(p_message || utl_tcp.crlf));
END;

PROCEDURE write_raw(p_conn IN OUT NOCOPY utl_smtp.connection
,p_message IN RAW) IS
BEGIN
utl_smtp.write_raw_data(p_conn,
p_message);
END;

FUNCTION begin_mail(p_smtp_host IN VARCHAR2
,p_smtp_port IN NUMBER DEFAULT 25
,p_from IN VARCHAR2
,p_to IN VARCHAR2
,p_cc IN VARCHAR2 DEFAULT NULL
,p_bcc IN VARCHAR2 DEFAULT NULL) RETURN utl_smtp.connection IS
l_conn utl_smtp.connection;
my_from VARCHAR2(32767) := p_from;
my_to VARCHAR2(32767) := p_to;
my_cc VARCHAR2(32767) := p_cc;
my_bcc VARCHAR2(32767) := p_bcc;
BEGIN
--begin session
log('begin session');
l_conn := utl_smtp.open_connection(p_smtp_host,
p_smtp_port);
utl_smtp.helo(l_conn,
p_smtp_host);

utl_smtp.command(l_conn,
'AUTH LOGIN',
'');
utl_smtp.command(l_conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('EBSADMIN'))));
utl_smtp.command(l_conn,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('Lct@181113'))));

utl_smtp.mail(l_conn,
get_address(my_from));

WHILE (my_to IS NOT NULL)
LOOP
utl_smtp.rcpt(l_conn,
get_address(my_to));
END LOOP;

WHILE (my_cc IS NOT NULL)
LOOP
utl_smtp.rcpt(l_conn,
get_address(my_cc));
END LOOP;

WHILE (my_bcc IS NOT NULL)
LOOP
utl_smtp.rcpt(l_conn,
get_address(my_bcc));
END LOOP;

RETURN l_conn;
END;

PROCEDURE write_header(p_conn IN OUT NOCOPY utl_smtp.connection
,p_from IN VARCHAR2
,p_to IN VARCHAR2
,p_cc IN VARCHAR2 DEFAULT NULL
,p_bcc IN VARCHAR2 DEFAULT NULL
,p_subject IN VARCHAR2
,p_mime_type IN VARCHAR2 DEFAULT 'text/plain') IS
l_host_name VARCHAR2(64);
BEGIN
--write header
log('write header');
SELECT v.instance_name
INTO l_host_name
FROM v$instance v;
utl_smtp.open_data(p_conn);

write_text(p_conn,
'Date: ' || to_char(SYSDATE,
'yyyy-mm-dd HH24:MI:SS'));

write_text(p_conn,
'From: ' || p_from);

write_text(p_conn,
'To: ' || p_to);

write_text(p_conn,
'CC: ' || p_cc);

write_text(p_conn,
'BCC: ' || p_bcc);

write_raw(p_conn,
utl_raw.cast_to_raw(convert('Subject: ' || '[' || l_host_name || ']' || p_subject || utl_tcp.crlf,
'UTF8')));
write_text(p_conn,
'Content-Type: ' || p_mime_type);

utl_smtp.write_data(p_conn,
utl_tcp.crlf);
END;

PROCEDURE write_body(p_conn IN OUT NOCOPY utl_smtp.connection
,p_content IN CLOB
,p_mime_type IN VARCHAR2 DEFAULT /*'text/plain'*/'text/html; charset=UTF-8') IS
l_offset NUMBER := 1;
l_amount NUMBER;
BEGIN
--write body
log('write body');
write_text(p_conn,
cux_send_attachment_common.first_boundary);
write_text(p_conn,
'Content-Type: ' || p_mime_type);
utl_smtp.write_data(p_conn,
utl_tcp.crlf);
l_offset := 1;
WHILE l_offset < dbms_lob.getlength(p_content)
LOOP
l_amount := dbms_lob.instr(dbms_lob.substr(p_content,
20000,
l_offset),
'</tr>',
1) + 4;
IF l_amount = 4 THEN
l_amount := dbms_lob.getlength(p_content) - l_offset + 1;
END IF;

write_raw(p_conn,
utl_raw.cast_to_raw(dbms_lob.substr(p_content,
l_amount,
l_offset)));
l_offset := l_offset + l_amount;
END LOOP;

utl_smtp.write_data(p_conn,
utl_tcp.crlf);
END;

PROCEDURE write_files(p_conn IN OUT NOCOPY utl_smtp.connection
,p_files IN cux_send_attachment_common.tab_of_attachments) IS
l_lob_size NUMBER;
l_buff_size NUMBER;
l_raw RAW(2000);
l_ind NUMBER;
l_attach_mime VARCHAR2(256);

BEGIN
--write files
log('write files');

IF p_files.count > 0 THEN
-- loop through attachments
FOR i IN 1 .. p_files.count
LOOP
IF p_files(i).attach_filename IS NULL THEN
continue;
END IF;
l_attach_mime := nvl(p_files(i).attach_mime, cux_file_utl.get_content_type(p_files(i).attach_filename));

write_text(p_conn,
cux_send_attachment_common.first_boundary);

write_raw(p_conn,
utl_raw.cast_to_raw('Content-Type: ' || l_attach_mime || '; name="' || p_files(i).attach_filename || '"' ||
utl_tcp.crlf));

write_raw(p_conn,
utl_raw.cast_to_raw('Content-Transfer-Encoding: base64' || utl_tcp.crlf));

/*write_raw(p_conn,
utl_raw.cast_to_raw('Content-Disposition: inline; filename="' || p_files(i).attach_filename || '"' || utl_tcp.crlf));*/
write_raw(p_conn,
utl_raw.cast_to_raw('Content-Disposition: attachment; filename="' || p_files(i).attach_filename || '"' || utl_tcp.crlf));

utl_smtp.write_data(p_conn,
utl_tcp.crlf);

l_ind := 1;
l_buff_size := 57;
l_raw := NULL;
l_lob_size := dbms_lob.getlength(p_files(i).attach_blob);
WHILE l_ind < l_lob_size
LOOP
dbms_lob.read(p_files(i).attach_blob,
l_buff_size,
l_ind,
l_raw);
write_raw(p_conn,
utl_encode.base64_encode(l_raw));

utl_smtp.write_data(p_conn,
utl_tcp.crlf);
l_raw := NULL;
l_ind := l_ind + l_buff_size;
END LOOP;

utl_smtp.write_data(p_conn,
utl_tcp.crlf);

END LOOP;

END IF;
write_text(p_conn,
cux_send_attachment_common.last_boundary);

END;

PROCEDURE end_mail(p_conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
--end mail
log('end mail');
utl_smtp.close_data(p_conn);

utl_smtp.quit(p_conn);
END;

PROCEDURE send_mail_files(p_smtp_host IN VARCHAR2 DEFAULT g_smtp_host
,p_smtp_port IN NUMBER DEFAULT g_smtp_port--25
,p_from IN VARCHAR2 DEFAULT g_from
,p_to IN VARCHAR2
,p_cc IN VARCHAR2 DEFAULT NULL
,p_bcc IN VARCHAR2 DEFAULT NULL
,p_subject IN VARCHAR2
,p_content IN CLOB DEFAULT NULL
,p_content_type IN VARCHAR2 DEFAULT 'text/html; charset=UTF-8'
,p_files IN cux_send_attachment_common.tab_of_attachments) IS

l_conn utl_smtp.connection;
BEGIN

l_conn := begin_mail(p_smtp_host => p_smtp_host,
p_smtp_port => p_smtp_port,
p_from => p_from,
p_to => p_to,
p_cc => p_cc,
p_bcc => p_bcc);
write_header(p_conn => l_conn,
p_from => p_from,
p_to => p_to,
p_cc => p_cc,
p_bcc => p_bcc,
p_subject => p_subject,
p_mime_type => cux_send_attachment_common.multipart_mime_type);

write_body(p_conn => l_conn,
p_content => p_content,
p_mime_type => p_content_type);


write_files(p_conn => l_conn,
p_files => p_files);

end_mail(p_conn => l_conn);

EXCEPTION
WHEN no_data_found THEN
/*write_text(l_conn,
NULL);*/
log('Error number is ' || SQLCODE);
log('Error message is ' || substr(SQLERRM,
1,
100));
RAISE fnd_api.g_exc_unexpected_error;
WHEN OTHERS THEN
/*write_text(l_conn,
NULL);*/
log('Error number is ' || SQLCODE);
log('Error message is ' || substr(SQLERRM,
1,
100));
RAISE fnd_api.g_exc_error;
END;

END cux_send_attachment_common;