how to export data from log table to email body in oracle

email oracle

is there a way in oracle how to export data from table to email? The thing is, I have a log table, where I keep failed logs. I would like to have a procedure which check if there are any logs on the day before, if yes then sends them in an email message. The result should look something like:

Log_id - Procedure_name - Fail_type - Message
Log_id - Procedure_name - Fail_type - Message
Log_id - Procedure_name - Fail_type - Message
Log_id - Procedure_name - Fail_type - Message

this is how my procedure looks like now

PROCEDURE send_mail(v_recipient VARCHAR2
                     ,v_subject   VARCHAR2
                     ,v_text      VARCHAR2
                     ,v_from      VARCHAR2 DEFAULT '[email protected]') IS
    v_mail_host VARCHAR2(30) := 'hgiugiuyg';
    v_mail_conn utl_smtp.connection;
    crlf        VARCHAR2(2) := chr(13) || chr(10);
  BEGIN
    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);
    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 || crlf ||
                   v_text || crlf);
      utl_smtp.quit(v_mail_conn);
  EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
      raise_application_error(-20000, 'Unable to send mail', TRUE);
  END;

Solution 1:

For any mails from Oracle I use this generic procedure in PL/SQL Package:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
/

CREATE OR REPLACE PACKAGE Mailing AS

PRIORITY_HIGH           CONSTANT INTEGER := 1;
PRIORITY_NORMAL         CONSTANT INTEGER := 3;
PRIORITY_LOW            CONSTANT INTEGER := 5;

PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN OUT CLOB, 
    ToMail IN VARCHAR_TABLE_TYPE,   
    FromMail IN VARCHAR2, FromName IN VARCHAR2,
    PRIORITY IN INTEGER DEFAULT PRIORITY_NORMAL,
    FileName IN VARCHAR2 DEFAULT NULL, 
    MimeType IN VARCHAR2 DEFAULT NULL, --> determines the MIME-Type of binary attachment "BinAttachment"
    TxtAttachment IN CLOB DEFAULT NULL, 
    BinAttachment IN BLOB DEFAULT NULL);


END Mailing;
/

CREATE OR REPLACE PACKAGE BODY Mailing AS

PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN OUT CLOB, 
    ToMail IN VARCHAR_TABLE_TYPE,   
    FromMail IN VARCHAR2, FromName IN VARCHAR2,
    PRIORITY IN T_MAIL_PRIORITY DEFAULT PRIORITY_NORMAL,
    FileName IN VARCHAR2 DEFAULT NULL, 
    MimeType IN VARCHAR2 DEFAULT NULL,
    TxtAttachment IN CLOB DEFAULT NULL, 
    BinAttachment IN BLOB DEFAULT NULL) IS

    SMTP_PORT               CONSTANT INTEGER := 25;
    SMTP_SERVER             CONSTANT VARCHAR2(50):= 'mailhost';
    MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '====Multipart.Boundary.689464861147414354====';

    con UTL_SMTP.CONNECTION;
    ret UTL_SMTP.REPLY;
    Charset VARCHAR2(20);
    Footer VARCHAR2(1000);
    Recipients VARCHAR2(1000);

    LobLen INTEGER;
    amount INTEGER := 8000;
    BUFFER VARCHAR2(32000);
    BUFFER_B RAW(48);
    OFFSET INTEGER := 1;
    isHTML BOOLEAN := REGEXP_LIKE(DBMS_LOB.SUBSTR(Message, 1000, 1), '(< *html)|(< *body)', 'i');

BEGIN

    SELECT UTL_I18N.MAP_CHARSET(VALUE)
    INTO Charset
    FROM NLS_DATABASE_PARAMETERS
    WHERE parameter = 'NLS_CHARACTERSET';

    -- Append common footer to mail 
    Footer := 'Message from '||SYS_CONTEXT('USERENV', 'DB_NAME')||' sent at '||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
    IF isHTML THEN
        Message := REPLACE(message, '</body>', '<p>'||Footer||'</p></body>');
    END IF;

    -- setup mail header
    con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    ret := UTL_SMTP.HELO(con, SYS_CONTEXT('USERENV', 'DB_DOMAIN'));
    ret := UTL_SMTP.MAIL(con, FromMail);
    FOR i IN ToMail.FIRST..ToMail.LAST LOOP
        Recipients := Recipients ||ToMail(i)||',';
        ret := UTL_SMTP.RCPT(con, ToMail(i));
    END LOOP;
    ret := UTL_SMTP.OPEN_DATA(con);

    IF CONVERT(FromName, 'US7ASCII') = FromName THEN
        UTL_SMTP.WRITE_DATA(con, 'From: "'||FromName||'" <'||FromMail||'>'||UTL_TCP.CRLF);
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'From: =?UTF-8?B?'|| UTL_ENCODE.TEXT_ENCODE(FromName, 'AL32UTF8', UTL_ENCODE.BASE64) ||'?= <'||FromMail||'>'||UTL_TCP.CRLF);
    END IF; 
    UTL_SMTP.WRITE_DATA(con, 'To: '||REGEXP_REPLACE(Recipients, ',$')||UTL_TCP.CRLF);
    IF CONVERT(Subject, 'US7ASCII') = Subject THEN
        UTL_SMTP.WRITE_DATA(con, 'Subject: '||Subject||UTL_TCP.CRLF);
    ELSE
        UTL_SMTP.WRITE_DATA(con, 'Subject: =?UTF-8?B?'|| REPLACE(REPLACE(UTL_ENCODE.TEXT_ENCODE(Subject, 'AL32UTF8', UTL_ENCODE.BASE64), CHR(13), NULL), CHR(10), NULL) ||'?='||UTL_TCP.CRLF);
    END IF;
    UTL_SMTP.WRITE_DATA(con, 'Date: '||TO_CHAR(CURRENT_TIMESTAMP, 'Dy, DD Mon YYYY hh24:mi:ss TZHTZM', 'NLS_DATE_LANGUAGE = American')||UTL_TCP.CRLF);  
    UTL_SMTP.WRITE_DATA(con, 'X-Priority: '||PRIORITY||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(con, 'MIME-Version: 1.0' || UTL_TCP.CRLF);

    IF FileName IS NOT NULL THEN
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: multipart/mixed; boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: inline'|| UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
    END IF;

    IF isHTML THEN
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: text/html; charset='||Charset || UTL_TCP.CRLF);
    ELSE 
        UTL_SMTP.WRITE_DATA(con, 'Content-Type: text/plain; charset='||Charset || UTL_TCP.CRLF);
    END IF;
    UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: inline'|| UTL_TCP.CRLF);


    -- Mail Body
    UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    LobLen := DBMS_LOB.GETLENGTH(Message);
    LOOP
        EXIT WHEN OFFSET > LobLen;
        DBMS_LOB.READ(Message, amount, OFFSET, BUFFER);
        UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
        OFFSET := OFFSET + amount;
    END LOOP;   
    UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    IF NOT isHTML THEN
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, Footer);
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
    END IF;

    IF FileName IS NOT NULL THEN
        -- Mail Attachment
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);

        OFFSET := 1;
        IF TxtAttachment IS NOT NULL THEN
            UTL_SMTP.WRITE_DATA(con, 'Content-Type: text/plain; charset='||Charset|| UTL_TCP.CRLF);
            UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: attachment; filename="'||Filename||'"'|| UTL_TCP.CRLF);
            UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);                         
            LobLen := DBMS_LOB.GETLENGTH(TxtAttachment);
            LOOP
                EXIT WHEN OFFSET > LobLen;
                DBMS_LOB.READ(TxtAttachment, amount, OFFSET, BUFFER);
                UTL_SMTP.WRITE_RAW_DATA(con, UTL_RAW.CAST_TO_RAW(BUFFER));
                OFFSET := OFFSET + amount;
            END LOOP;
        ELSIF BinAttachment IS NOT NULL THEN
            UTL_SMTP.WRITE_DATA(con, 'Content-Type: '||MimeType||'; name="'||Filename||'"'|| UTL_TCP.CRLF);
            UTL_SMTP.WRITE_DATA(con, 'Content-Disposition: attachment; filename="'||Filename||'"'|| UTL_TCP.CRLF);
            UTL_SMTP.write_data(con, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
            UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);            
            amount := 48; -- must be a whole multiple of 3
            LobLen := DBMS_LOB.GETLENGTH(BinAttachment);
            LOOP
                EXIT WHEN OFFSET > LobLen;
                DBMS_LOB.READ(BinAttachment, amount, OFFSET, BUFFER_B);
                UTL_SMTP.WRITE_RAW_DATA(con, UTL_ENCODE.BASE64_ENCODE(BUFFER_B));
                OFFSET := OFFSET + amount;
            END LOOP;       
        END IF;
        UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);
        UTL_SMTP.WRITE_DATA(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);
    END IF;

    -- finish mail
    ret := UTL_SMTP.CLOSE_DATA(con);
    ret := UTL_SMTP.QUIT(con);

EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
        UTL_SMTP.QUIT(con);
END SendMail;

END Mailing;
/

It provides some extras:

  • You can optionally attach a file (txt or binary, but only one file is supported)
  • From and Subject may contain Non-ASCII characters like öäü (of course the mail Body may also)
  • Multiple recipients
  • Automatically use correct character set from database
  • Automatically detects plain-text or HTML mail body
  • Common footer to indicate mail

Note, don't miss the empty lines UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);, they are required for proper mail.

Then you can use the procedure for example like this:

DECLARE
    Message CLOB;
BEGIN

    FOR aMsg IN (SELECT Log_id, Procedure_name, Fail_type, Message FROM log_messages ORDER BY Log_id) LOOP
        Message := Message || aMsg.Log_id ||' - '|| aMsg.Procedure_name ||' - '|| aMsg.Fail_type ||' - '|| aMsg.Message || CHR(13);
    end loop;

    SendMail(
        Subject => 'You got some logs', 
        Message => Message, 
        ToMail => VARCHAR_TABLE_TYPE('[email protected]'),   
        FromMail => '[email protected]', 
        FromName => 'Oracle User: '||USER);
END;

or you can put logs as attachment like this:

DECLARE
    Message CLOB;
    Attachment CLOB;
BEGIN
    Message := 'Open attachment to see log file';
    FOR aMsg IN (SELECT Log_id, Procedure_name, Fail_type, Message FROM log_messages ORDER BY Log_id) LOOP
        Attachment := Attachment || aMsg.Log_id ||' - '|| aMsg.Procedure_name ||' - '|| aMsg.Fail_type ||' - '|| aMsg.Message || CHR(13);
    end loop;

    Mailing.SendMail(
        Subject => 'You got some logs', 
        Message => Message, 
        ToMail => VARCHAR_TABLE_TYPE('[email protected]'),   
        FromMail => '[email protected]', 
        FromName => 'Oracle User: '||USER,
        TxtAttachment => Attachment,
        FileName => 'logfile.txt');
END;

Solution 2:

You can generate an HTML script and add it in mail. Like if you want a tabular format, you can create the HTML script and attach it in mail.

Take the following for refference

DECLARE
   p_message_body   CLOB                := EMPTY_CLOB ();
   p_smtp_host      VARCHAR2 (20)       := <SMTP_SERVER_IP>;
   p_smtp_port      VARCHAR2 (10)       := '25';
   p_message_type   VARCHAR2 (100)      := ' text/html';
   crlf             VARCHAR2 (2)        := UTL_TCP.crlf;
   ls_dt_start      VARCHAR2 (50);
   ls_dt_end        VARCHAR2 (50);
   l_mail_conn      UTL_SMTP.connection;
   pf_to_name       VARCHAR2 (100);
BEGIN
   BEGIN
      SELECT TO_CHAR (SYSDATE - 1, 'HH12:MI:SS AM'),
             TO_CHAR (SYSDATE + 1, 'HH12:MI:SS AM')
        INTO ls_dt_start,
             ls_dt_end
        FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   p_message_body :=
      '<HTML> <HEAD> <STYLE> table, th, td { border: 1px solid black; border-collapse: collapse; } </STYLE> </HEAD> ';
   p_message_body :=
         p_message_body
      || '<BODY> <P> <FONT COLOR="BLACK",FONT FACE ="ARIAL",FONT SIZE ="2.5"> Hello , <BR/><BR/><BR/> Dummy Message. Find Table Below ';
   p_message_body :=
         p_message_body
      || '<BR/><BR/> <TABLE> <TR> <TH>Start Time</TH> <TH>End Time</TH> </TR> <TR> <TD>'
      || ls_dt_start
      || '</TD> <TD>'
      || ls_dt_end
      || '</TD> </TR> </TABLE> </BODY> </HTML>';
   l_mail_conn := UTL_SMTP.open_connection (p_smtp_host, p_smtp_port);
   UTL_SMTP.helo (l_mail_conn, p_smtp_host);
   UTL_SMTP.mail (l_mail_conn, '[email protected]');
   pf_to_name := '[email protected]';
   UTL_SMTP.rcpt (l_mail_conn, '[email protected]');
   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw ('To: ' || pf_to_name || crlf)
                           );
   UTL_SMTP.write_raw_data
                 (l_mail_conn,
                  UTL_RAW.cast_to_raw (   'Date: '
                                       || TO_CHAR
                                                 (SYSDATE,
                                                  'Dy, DD Mon YYYY hh24:mi:ss'
                                                 )
                                       || crlf
                                      )
                 );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw (   'From: '
                                                 || '[email protected]'
                                                 || crlf
                                                )
                           );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw (   'Subject: '
                                                 || 'Test {'
                                                 || TO_CHAR (SYSDATE,
                                                             'DD Mon YYYY'
                                                            )
                                                 || '}'
                                                 || crlf
                                                )
                           );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw ('MIME-Version: 1.0' || crlf)
                           );
   UTL_SMTP.write_raw_data
      (l_mail_conn,
       UTL_RAW.cast_to_raw
                      (   'Content-Type: multipart/mixed; boundary="SECBOUND"'
                       || crlf
                       || crlf
                      )
      );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw ('--SECBOUND' || crlf)
                           );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw (   'Content-Type: '
                                                 || p_message_type
                                                 || crlf
                                                 || crlf
                                                )
                           );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw (p_message_body || crlf)
                           );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw ('--SECBOUND' || crlf)
                           );
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw ('--SECBOUND' || crlf)
                           );
   --Defining content type as attachment and specifying the filename.
   UTL_SMTP.write_raw_data (l_mail_conn, UTL_RAW.cast_to_raw ('' || crlf));
   UTL_SMTP.write_raw_data (l_mail_conn,
                            UTL_RAW.cast_to_raw ('--SECBOUND' || crlf)
                           );
   --Close connection and send mail.
   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;

You can substitute the data you want with your table.

Related

Wix, custom dialog when previous version exists
C# WPF Navigation Between Pages (Views)
Searching values via a datagridview
How can we execute WebUI feature file against multiple browsers using parallel runner or distributed testing?
How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?
ES keeps returning every document
Installing module from GitHub through Jupyter notebook
Multiple Forms, One Project [duplicate]
repaint swing button with different gradient
PHP Regular expression to match keyword outside HTML tag <a>
QtCore.QObject.connect in a loop only affects the last instance
What is the purpose of allocating a specific amount of memory for arrays in C++?