生成 >CSV 文件并使用 Oracle PLSQL 自动发送电子邮件

我尝试生成。csv文件列表的EQUIPMENT_NAME和EQC_SERVER和自动发送列表每2周特定的电子邮件地址。

然而，从我的代码，我只能发送电子邮件，而不是在。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            := 'TEST@OSRAM-OS.COM';
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;

谢谢。

如果在数据库中安装了APEX，那么创建CSV文件并将其作为附件添加是很简单的。下面是如何使用APEX_DATA_EXPORT包的示例，然后使用生成的CSV BLOB，并将其作为附件添加到使用APEX_MAIL包的电子邮件中。

DECLARE
l_context   apex_exec.t_context;
l_export    apex_data_export.t_export;
l_mail_id   NUMBER;
BEGIN
apex_session.create_session (100, 2, 'ReportEmailUser');
l_context :=
apex_exec.open_query_context (
p_location    => apex_exec.c_location_local_db,
p_sql_query   => 'select * from emp');
l_export :=
apex_data_export.export (p_context => l_context, p_format => apex_data_export.c_format_csv);
apex_exec.close (l_context);
l_mail_id :=
APEX_MAIL.SEND (p_to          => 'youremail@example.com',
p_from        => 'noreply@example.com',
p_body        => 'Report is attached',
p_body_html   => '<h2>Report is attached</h2>',
p_subj        => 'Example Report');
APEX_MAIL.ADD_ATTACHMENT (p_mail_id      => l_mail_id,
p_attachment   => l_export.content_blob,
p_filename     => 'example_report.csv',
p_mime_type    => 'text/csv');
apex_mail.push_queue;
apex_session.delete_session;
EXCEPTION
WHEN OTHERS
THEN
apex_exec.close (l_context);
RAISE;
END;

对于生成csv文件来说，这是一个太复杂的方法，对于每个新表，您都必须修改代码。下载"sqlcl"我经常使用它在linux上从oracle表生成csv文件。

https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/

相关内容

最新更新

热门标签：