To generate >CSV file and automate send an email using Oracle PLSQL

Question

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.

EJ Egyed · Accepted Answer · 2022-05-11 19:55:06Z

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;

Sami · Accepted Answer · 2022-10-23 23:45:03Z

0

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/

answered Oct 23, 2022 at 23:45

Sami

907 bronze badges

Collectives™ on Stack Overflow

To generate >CSV file and automate send an email using Oracle PLSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related