我尝试生成 EQUIPMENT_NAME 和 EQC_SERVER 的 .CSV 文件列表，并每两周自动将列表发送到特定电子邮件地址。
但是，从我的代码中我只能发送电子邮件，但不能发送 .CSV 文件。
任何人都可以建议/协助如何生成 .CSV 文件并作为电子邮件发送吗？
下面是我的代码：
create or replace PACKAGE BODY CIM_SEC_EQC AS
PROCEDURE GET_RECIPIENT_ADD(outCursor out sys_refcursor) AS
v_cursor sys_refcursor;
v_Sql long;
Begin
v_Sql := 'SELECT * FROM OS_ALERT_MAIL_RECIPIENT ORDER BY 1';
OPEN v_cursor
FOR v_Sql;
outCursor := v_Cursor;
END GET_RECIPIENT_ADD;
PROCEDURE GET_JOB AS
CRLF VARCHAR2(2) := CHR(13) || CHR(10);
V_SQLERRM VARCHAR2(200) := '';
V_RCPS VARCHAR2(1000) := '';
V_APP CLOB;
V_PRO CLOB;
V_JOB CLOB;
V_ALL CLOB;
CURSOR C_RCP IS
SELECT * FROM OS_ALERT_MAIL_RECIPIENT ORDER BY 1;
CURSOR C_JOB IS
SELECT EQUIPMENT_NAME,HOST_NAME FROM REGISTERED_APPS ORDER BY 1;
BEGIN
FOR RCP IN C_RCP
LOOP
V_RCPS := V_RCPS||RCP.EMAIL||';';
END LOOP;
IF(V_RCPS != '') THEN
V_RCPS := SUBSTR(V_RCPS, 1, LENGTH(V_RCPS) - 1);
END IF;
FOR JOB_STAT IN C_JOB
LOOP
V_JOB := V_JOB||'<tr><td>'||JOB_STAT.EQUIPMENT_NAME||'</td><td>'||JOB_STAT.HOST_NAME||'</td></tr>';
END LOOP;
IF(V_JOB != ' ') THEN
V_JOB := '<h3>EQC AND SERVER LIST : </h3><table><tr><th>EQUIPMENT ID</th><th>SERVER NAME</th></tr>'||V_JOB||'</table>';
END IF;
IF(V_JOB != ' ')THEN
V_ALL := '<!DOCTYPE html>
<html>
<head>
<style type="text/css">
table {
width:100%;
}
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
padding: 15px;
text-align: left;
}
table tr:nth-child(even) {
background-color: #eee;
}
table tr:nth-child(odd) {
background-color: #fff;
}
table th {
background-color: black;
color: white;
}
</style>
</head>
<body>
<div>';
IF(V_JOB != ' ')THEN
V_ALL := V_ALL||V_JOB;
END IF;
V_ALL := V_ALL||'</div>
</body>
</html>';
SEND_MAIL(V_RCPS,V_ALL);
END IF;
END GET_JOB;
PROCEDURE SEND_MAIL(VTO IN VARCHAR2, VTEXT IN VARCHAR2) AS
MAIL_CONN UTL_SMTP.CONNECTION;
V_MESSAGE VARCHAR2(32000);
V_SENDER VARCHAR2(65);
VEMPF VARCHAR2(2000);
ILEN NUMBER(4);
S VARCHAR2(2000);
IPOS NUMBER(4);
CRLF VARCHAR2(2) := CHR(13) || CHR(10);
VSUBJECT VARCHAR2(120);
BEGIN
V_SENDER := '[email protected] /cdn-cgi/l/email-protection';
MAIL_CONN := UTL_SMTP.OPEN_CONNECTION('intrelay.osram.net', 25);
UTL_SMTP.HELO(MAIL_CONN, 'intrelay.osram.net');
UTL_SMTP.MAIL(MAIL_CONN, V_SENDER);
VSUBJECT := 'EQC AND SERVER LIST[THIS IS A SYSTEM GENERATED EMAIL DO NOT REPLY TO THIS EMAIL]';
VEMPF := VTO;
ILEN := LENGTH(VEMPF);
IPOS := 1;
WHILE (ILEN > 0) AND (IPOS > 0)
LOOP
IPOS := INSTR(VEMPF, ';');
IF IPOS > 0 THEN
S := SUBSTR(VEMPF, 1, IPOS - 1);
UTL_SMTP.RCPT(MAIL_CONN, '<' || S || '>');
VEMPF := SUBSTR(VEMPF, IPOS + 1, ILEN - IPOS);
ILEN := LENGTH(VEMPF);
END IF;
END LOOP;
IF (IPOS = 0) AND (ILEN > 0) THEN
UTL_SMTP.RCPT(MAIL_CONN, '<' || VEMPF || '>');
END IF;
V_MESSAGE := V_MESSAGE || 'From: ' || V_SENDER || CRLF;
V_MESSAGE := V_MESSAGE || 'To: ' || '<' || VTO || '>' || CRLF;
V_MESSAGE := V_MESSAGE || 'Subject: ' || VSUBJECT || CRLF;
V_MESSAGE := V_MESSAGE || 'Mime-Version: 1.0' || CRLF;
V_MESSAGE := V_MESSAGE || 'Content-Type: text/html; charset=US-ASCII' || CRLF;
V_MESSAGE := V_MESSAGE || VTEXT;
UTL_SMTP.DATA(MAIL_CONN, V_MESSAGE);
UTL_SMTP.QUIT(MAIL_CONN);
EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(MAIL_CONN);
END SEND_MAIL;
END CIM_SEC_EQC;
谢谢。
