当前位置： 文江博客 话题详情

为了生成＆gt; csv文件并自动使用Oracle PLSQL发送电子邮件

发布于 2025-01-27 23:36:34 字数 4914 浏览 6 评论 0 原文

我尝试生成设备和eqc_server的.CSV文件列表，并自动每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            := '[email protected]';
    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;

谢谢。

原文

I try to generate .CSV file lists of EQUIPMENT_NAME and EQC_SERVER and automate sending the list every 2 weeks to specific email address.

However, from my code i only able to send email but not in .CSV file.
Can anyone advise/assist on how to generate .CSV file and send as an email?

Below is my code:

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]';
    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;

Thank You.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

装纯掩盖桑 2025-02-03 23:36:34

如果您在数据库中安装了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          => '[email protected]',
                        p_from        => '[email protected]',
                        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;

If you have APEX installed in your database, creating the CSV file and adding it as an attachment is trivial. Below is an example of how to use the APEX_DATA_EXPORT package, then use the CSV BLOB that gets generated, and add it as an attachment to an email using the APEX_MAIL package.

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          => '[email protected]',
                        p_from        => '[email protected]',
                        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;
回复 原文
自演自醉 2025-02-03 23:36:34

这太复杂了，无法生成CSV文件，对于每个新表，您必须修改代码。
从Oracle下载“ SQLCL”命令行实用程序，我定期使用此功能从Oracle表中生成CSV文件。

This is too complicated method to generate csv files and for each new table you will have to modify the code.
Download "sqlcl" command line utility from oracle , I use this regularly to generate csv files on linux from oracle tables.

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

回复 原文
~没有更多了~

关于作者

汐鸠

暂无简介

文章
评论
29 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文