oracle中日志表到邮件体数据导出

ykejflvf  于 2023-02-15  发布在  Oracle
关注(0)|答案(2)|浏览(82)

在oracle中有没有一种方法可以将数据从表导出到电子邮件？问题是，我有一个日志表，在那里我保存了失败的日志。我想有一个过程来检查前一天是否有任何日志，如果有，然后在电子邮件中发送它们。结果应该看起来像这样：

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

这是我现在的程序

PROCEDURE send_mail(v_recipient VARCHAR2
                     ,v_subject   VARCHAR2
                     ,v_text      VARCHAR2
                     ,v_from      VARCHAR2 DEFAULT 'hsdfafjh@jhh.jij') 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;
oracle

2条答案

按热度按时间
ozxc1zmp

ozxc1zmp1#

对于来自Oracle的任何邮件，我在PL/SQL程序包中使用以下通用过程：

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;
/

它提供了一些额外功能：

  • 您可以选择附加文件（txt或二进制，但仅支持一个文件）
  • From和Subject可以包含非ASCII字符，如öäü（当然邮件正文也可以）
  • 多个收件人
  • 自动使用数据库中的正确字符集
  • 自动检测纯文本或HTML邮件正文
  • 用于指示邮件的通用页脚

注意，不要错过空行UTL_SMTP.WRITE_DATA(con, UTL_TCP.CRLF);，它们是正确邮寄所必需的。
然后，您可以使用如下所示的过程：

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('hsdfafjh@mydomain.com'),   
        FromMail => 'noreply@mydomain.com', 
        FromName => 'Oracle User: '||USER);
END;

或者您可以将日志作为附件，如下所示：

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('hsdfafjh@mydomain.com'),   
        FromMail => 'noreply@mydomain.com', 
        FromName => 'Oracle User: '||USER,
        TxtAttachment => Attachment,
        FileName => 'logfile.txt');
END;
    • 更新日期：**

如果附件以文件形式存在于您的服务器上，请使用以下函数将其转换为BLOB/CLOB：

FUNCTION TextFile2CLOB(FolderName IN VARCHAR2, FileName IN VARCHAR2) RETURN CLOB IS

    xbfile   BFILE; 
    xclob    CLOB; 
    destOffset INTEGER := 1; 
    srcOffset INTEGER := 1; 
    langContext INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; 
    warning INTEGER; 
    
BEGIN 
    xbfile := BFILENAME (FolderName, FileName); 
    DBMS_LOB.OPEN (xbfile); 
    DBMS_LOB.CREATETEMPORARY (xclob, TRUE, DBMS_LOB.SESSION);   
    DBMS_LOB.LOADCLOBFROMFILE (
        dest_lob => xclob, 
        src_bfile => xbfile, 
        amount => DBMS_LOB.GETLENGTH(xbfile), 
        dest_offset => destOffset, 
        src_offset => srcOffset, 
        bfile_csid => DBMS_LOB.DEFAULT_CSID, 
        --bfile_csid => NLS_CHARSET_ID('AL32UTF8'),
        lang_context => langContext, 
        warning => warning); 
    DBMS_LOB.CLOSE (xbfile); 
    RETURN xclob; 
END TextFile2CLOB;

FUNCTION BinFile2BLOB(FolderName IN VARCHAR2, FileName IN VARCHAR2) RETURN BLOB IS

    xbfile   BFILE; 
    xblob    BLOB; 
    destOffset INTEGER := 1; 
    srcOffset INTEGER := 1; 
    
BEGIN 
    xbfile := BFILENAME (FolderName, FileName); 
    DBMS_LOB.OPEN (xbfile); 
    DBMS_LOB.CREATETEMPORARY(xblob, TRUE, DBMS_LOB.SESSION);    
    DBMS_LOB.LOADBLOBFROMFILE(
        dest_lob => xblob, 
        src_bfile => xbfile, 
        amount => DBMS_LOB.GETLENGTH(xbfile), 
        dest_offset => destOffset, 
        src_offset => srcOffset); 

    DBMS_LOB.CLOSE(xbfile);
    RETURN xblob; 
END BinFile2BLOB;
赞(0）分享  回复(0）举报  2023-02-15
7kjnsjlb

7kjnsjlb2#

你可以生成一个HTML脚本并将其添加到邮件中。就像你想要一个表格格式一样，你可以创建HTML脚本并将其附加到邮件中。
请参考以下内容

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, 'TEST@ORACLE.COM');
   pf_to_name := 'abc@xyz.com';
   UTL_SMTP.rcpt (l_mail_conn, 'abc@xyz.com');
   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: '
                                                 || 'TEST@ORACLE.COM'
                                                 || 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;

可以用表替换所需的数据。

赞(0）分享  回复(0）举报  2023-02-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com