有人能帮我UTL_SMTP。WRITE_DATA发送电子邮件附件。我不能发送多行代码内的附件。
当我写UTL_SMTP.WRITE_DATA来写入附件内的数据时，它不起作用。
下面是附加代码，当i=1时，它仅从第二个游标发送1行的附件。
快来人帮忙。
CREATE OR REPLACE PACKAGE BODY xx_common_alerts_pkg AS
gv_process_name CONSTANT VARCHAR2(100) := 'Common Alert Functionality';
PROCEDURE main (
po_errbuf OUT VARCHAR2,
po_retcode OUT NUMBER,
-- p_debug IN NUMBER,
p_alert_name IN VARCHAR2
) IS
lv_procedure CONSTANT VARCHAR2(200) := 'XX_COMMON_ALERTS_PKG.main';
lv_to_recipients alr_actions.to_recipients%TYPE;
lv_cc_recipients alr_actions.cc_recipients%TYPE;
lv_bcc_recipients alr_actions.bcc_recipients%TYPE;
lv_subject alr_actions.subject%TYPE;
lv_alert_id alr_alerts.alert_id%TYPE;
lv_row_count alr_action_set_checks.row_count%TYPE;
lv_check_id alr_action_set_checks.alert_check_id%TYPE;
v_clob CLOB := empty_clob();
v_clob_lines CLOB := empty_clob();
lv_mail_body_lines VARCHAR2(30000);
lv_ctxh dbms_xmlgen.ctxhandle;
lv_queryresult XMLTYPE;
lv_xslt_transform XMLTYPE;
lv_message VARCHAR2(30000);
lv_error_msg VARCHAR2(3000);
lv_inst_name VARCHAR2(50);
lv_from_email VARCHAR2(100);
lv_to_email VARCHAR2(100);
lv_recipients_mail VARCHAR2(100);
lv_status VARCHAR2(30000);
lv_alert_check_id VARCHAR2(2000);
lv_list_id alr_actions.list_id%TYPE;
lv_list_application_id alr_actions.list_application_id%TYPE;
lv_body alr_actions.body%TYPE;
lv_ret_message VARCHAR2(2000);
lv_ret_status VARCHAR2(2000);
lv_attach_tab t_attach_tab := t_attach_tab();
v_from VARCHAR2(80) := 'abc@gmail.com';
v_recipient VARCHAR2(80) := 'def@gmail.com';
v_subject VARCHAR2(80) := 'test';
v_mail_host VARCHAR2(80) := 'mlocalhost'; --localhost';
v_smtp_port NUMBER := 25;
v_mail_conn utl_smtp.connection;
l_step PLS_INTEGER := 12000;
crlf VARCHAR2(2) := chr(13)
|| chr(10);
v_len INTEGER;
v_index INTEGER := 1;
le_mail_excp EXCEPTION;
CURSOR c_get_alert_outputs (
pi_alert_id VARCHAR2
) IS
SELECT
name,
title
FROM
alr_alert_outputs
WHERE
alert_id = pi_alert_id
AND end_date_active IS NULL
ORDER BY
name DESC;
CURSOR c_output_lines (
pi_check_id NUMBER,
pi_row_number NUMBER
) IS
SELECT DISTINCT
value,
name
FROM
alr_output_history
WHERE
check_id = pi_check_id
AND row_number = pi_row_number
ORDER BY
name DESC;
BEGIN
SELECT
actions.to_recipients,
actions.cc_recipients,
actions.bcc_recipients,
actions.subject,
alr.alert_id,
actions.list_id,
actions.list_application_id,
actions.body
INTO
lv_to_recipients,
lv_cc_recipients,
lv_bcc_recipients,
lv_subject,
lv_alert_id,
lv_list_id,
lv_list_application_id,
lv_body
FROM
alr_alerts alr,
alr_actions actions
WHERE
alr.alert_name = p_alert_name
AND alr.alert_id = actions.alert_id
AND actions.name = 'Send Email'
AND actions.enabled_flag = 'Y'
AND actions.end_date_active IS NULL;
IF lv_list_id IS NOT NULL THEN
SELECT
to_recipients,
cc_recipients,
bcc_recipients
INTO
lv_to_recipients,
lv_cc_recipients,
lv_bcc_recipients
FROM
alr_distribution_lists
WHERE
list_id = lv_list_id
AND application_id = lv_list_application_id
AND enabled_flag = 'Y'
AND end_date_active IS NULL;
END IF;
SELECT
row_count,
check_id,
alert_check_id
INTO
lv_row_count,
lv_check_id,
lv_alert_check_id
FROM
alr_action_set_checks
WHERE
alert_id = lv_alert_id
AND alert_check_id = (
SELECT
MAX(alert_check_id)
FROM
alr_action_set_checks
WHERE
alert_id = lv_alert_id
);
-- lv_attach_tab.DELETE;
v_mail_conn := utl_smtp.open_connection(v_mail_host, 25);
utl_smtp.helo(v_mail_conn, v_mail_host);
utl_smtp.mail(v_mail_conn, v_from);
utl_smtp.rcpt(v_mail_conn, v_recipient);
FOR rec_alert_outputs IN c_get_alert_outputs(lv_alert_id) LOOP
v_clob := rec_alert_outputs.title
|| ','
|| v_clob;
END LOOP;
FOR i IN 1..lv_row_count LOOP
v_clob_lines := empty_clob();
FOR rec_lines IN c_output_lines(lv_check_id, i) LOOP
v_clob_lines := rec_lines.value
|| ','
|| v_clob_lines;
END LOOP;
utl_smtp.data(v_mail_conn, 'Date: '
|| TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
|| crlf
|| 'From: '
|| v_from
|| crlf
|| 'Subject: '
|| v_subject
|| crlf
|| 'To: '
|| v_recipient
|| crlf
|| 'MIME-Version: 1.0'
|| crlf
|| -- Use MIME mail standard
'Content-Type: multipart/mixed;'
|| crlf
|| ' boundary="-----SECBOUND"'
|| crlf
|| crlf
|| '-------SECBOUND'
|| crlf
|| 'Content-Type: text/plain;'
|| crlf
|| 'Content-Transfer_Encoding: 7bit'
|| crlf
|| crlf
|| lv_body
|| crlf
|| crlf
|| '-------SECBOUND'
|| crlf
|| 'Content-Type: text/plain;'
|| crlf
|| ' name="ASL_Mismatch.csv"'
|| crlf
|| 'Content-Transfer_Encoding: 8bit'
|| crlf
|| 'Content-Disposition: attachment;'
|| crlf
|| ' filename="Mismatch.csv"'
|| crlf
|| crlf
|| v_clob
|| crlf
|| v_clob_lines
|| crlf);
fnd_file.put_line(fnd_file.output, 'v_clob ' || v_clob);
utl_smtp.data(v_mail_conn, v_clob);
utl_smtp.data(v_mail_conn, v_clob_lines);
--UTL_SMTP.write_data(v_Mail_Conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(v_clob_lines, 1, i * 1))) || UTL_TCP.crlf);
--UTL_SMTP.write_data(v_Mail_Conn,UTL_RAW.cast_to_varchar2(v_clob_lines));
-- utl_smtp.write_data(v_Mail_Conn, v_clob_lines);
-- UTL_SMTP.write_data(v_Mail_Conn, DBMS_LOB.SUBSTR(v_clob_lines, 32000, v_index));
fnd_file.put_line(fnd_file.output, 'v_clob_lines '
|| v_clob_lines
|| ' Row: '
|| i);
END LOOP;
utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, '--'
|| ' boundary="-----SECBOUND"'
|| '--'
|| utl_tcp.crlf);
utl_smtp.close_data(v_mail_conn);
utl_smtp.quit(v_mail_conn);
EXCEPTION
WHEN OTHERS THEN
lv_error_msg := p_alert_name
|| '-'
|| lv_status
|| ': '
|| sqlerrm;
--debug(pi_message => lv_error_msg
-- );
END main;
END xx_common_alerts_pkg;
1条答案
按热度按时间mzsu5hc01#
与其调试你的代码，我不如和你分享我自己的sendmail函数，你可以用它来找出你做错了什么。一个让我立刻意识到的区别是我使用base64编码而不是8位编码，以防止附件中的任何内容扰乱SMTP。我确信有多种方法可以做到这一点，但这一方法已经为我工作了很多年：
这是HTML在身体启用。我有另一个是纯文本。我喜欢有HTML，所以我可以做一些简单的格式，如粗体和斜体等。
但是我的附件往往是文本，即使CSV或XML是为Excel准备的。所以，我总是使用mime
'text/plain'作为附件。将附件编译为CLOB并将其传递到附件参数之一。