通过Oracle存储过程将电子邮件.txt文件作为附件 [英] Email .txt file as an attachment through Oracle Stored Procedure

查看：204 发布时间：2017/8/9 23:12:17 oracle email stored-procedures oracle11g smtp

本文介绍了通过Oracle存储过程将电子邮件.txt文件作为附件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个.txt文件，其中我存储了我的数据分号分隔。
该文件存储在 Clob 中的后端。

文件：

ABCD; HEAD; 1.0; 00204; 18.05.2015; 00000000; 00000000000.00; 18.05.2015

我需要通过存储过程将此文本文件作为附件发送在Oracle中。

问题：

我将此编码文件附在电子邮件中： / p>

4546554C3B484541443B20202020202020312E303B30303634323B31382E30352E323031353B30303030303030313B30303030303030303032302E30303B31382E30352E323031350A4546554C3B444154413B20202020202020312E303B4546554C3B303030543130303030303335303B313B31382E30352E323031353B303030303032302C3030302E30303B504B523B3132363435363135313231313B303235303533313030310D0A

<强>步骤：

  CREATE OR REPLACE PROCEDURE SENDMAIL_ATT 
（
 MSG_FROM VARCHAR2，
 MSG_TO VARCHAR2， 
 MSG_SUBJECT VARCHAR2，
 MSG_TEXT VARCHAR2，
 MSG_ATT CLOB，
 ATT_FILENAME VARCHAR2 
）IS 
 
 V_MAILHOST VARCHAR2（50）：='mail_server ; 
 V_PORT NUMBER（2）：= 25; 
 V_HELO VARCHAR2（50）：='localhost'; 
 
 C UTL_TCP.CONNECTION; 
 RC INTEGER; 
 CRLF VARCHAR2（2）：= CHR（13）|| CHR（10）; 
 MESG VARCHAR2（32767）; 
 
 V_BUFFER_SIZE CONSTANT PLS_INTEGER：= 4000; 
 V_BUFFER_NEXT PLS_INTEGER：= 4000; 
 V_BODY_BUFFER VARCHAR2（8000 CHAR）; 
 BEGIN 
 
 C：= UTL_TCP.OPEN_CONNECTION（V_MAILHOST，V_PORT）; - 打开SMTP端口
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 RC：= UTL_TCP.WRITE_LINE（C，'HELO'|| V_HELO）; 
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 RC：= UTL_TCP.WRITE_LINE（C，'MAIL FROM：'|| MSG_FROM）; -----邮箱发送电子邮件
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 RC：= UTL_TCP.WRITE_LINE（C，'RCPT TO：'|| MSG_TO）; ----- MAIL BOX RECIEVING EMAIL 
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 RC：= UTL_TCP.WRITE_LINE（C，'DATA'）; ----- EMAIL MESSAGE BODY START 
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 RC：= UTL_TCP.WRITE_LINE（C，'Date：'|| 
 TO_CHAR（SYSDATE，'dd Mon yy hh24：mi：ss'））; 
 RC：= UTL_TCP.WRITE_LINE（C，'From：'|| MSG_FROM）; 
 RC：= UTL_TCP.WRITE_LINE（C，'MIME-Version：1.0'）; 
 RC：= UTL_TCP.WRITE_LINE（C，'To：'|| MSG_TO）; 
 RC：= UTL_TCP.WRITE_LINE（C，'Subject：'|| MSG_SUBJECT）; 
 RC：= UTL_TCP.WRITE_LINE（C，'Content-Type：multipart / mixed;'）; -----表示身体多于一个部分的
 RC：= UTL_TCP.WRITE_LINE（C，'boundary =----- SECBOUND）; -----用于分配身体部位的分离器
 RC：= UTL_TCP.WRITE_LINE（C）; -----不要删除此空白行 -  MIME标准的一部分
 RC：= UTL_TCP.WRITE_LINE（C，'------- SECBOUND'）; 
 RC：= UTL_TCP.WRITE_LINE（C，'Content-Type：text / html'）; -----第一身体部EMAIL TEXT MESSAGE 
 RC：= UTL_TCP.WRITE_LINE（C，'Content-Transfer-Encoding：7bit'）; 
 RC：= UTL_TCP.WRITE_LINE（C）; 
 RC：= UTL_TCP.WRITE_LINE（C，MSG_TEXT）; -----电子邮件消息的文本
 RC：= UTL_TCP.WRITE_LINE（C）; 
 RC：= UTL_TCP.WRITE_LINE（C，'------- SECBOUND'）; 
 RC：= UTL_TCP.WRITE_LINE（C，'Content-Type：text / plain;'）; -----第二身体部
 RC：= UTL_TCP.WRITE_LINE（C，'name ='|| ATT_FILENAME ||'）; 
 RC：= UTL_TCP.WRITE_LINE（C，'Content-Transfer_Encoding：7bit'）; 
 RC：= UTL_TCP.WRITE_LINE（C，'Content-Disposition：attachment;'）; -----显示这是附件
 RC：= UTL_TCP.WRITE_LINE（C，'filename ='|| ATT_FILENAME ||'）; -----建议的文件名称附件
 RC：= UTL_TCP.WRITE_LINE（C）; 
 
 --CLOB 
 FOR I IN 0 .. FLOOR（DBMS_LOB.GETLENGTH（MSG_ATT）/ V_BUFFER_SIZE）
 LOOP 
 DBMS_LOB.READ（MSG_ATT，
 V_BUFFER_NEXT，
 I * V_BUFFER_SIZE + 1，
 V_BODY_BUFFER）; 
 
 RC：= UTL_TCP.WRITE_TEXT（C，V_BODY_BUFFER）; 
 
 END LOOP; 
 
 RC：= UTL_TCP.WRITE_LINE（C）; 
 RC：= UTL_TCP.WRITE_LINE（C，'。'）; ----- EMAIL MESSAGE BODY END 
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 RC：= UTL_TCP.WRITE_LINE（C，'QUIT'）; ----- ENDS EMAIL TRANSACTION 
 DBMS_OUTPUT.PUT_LINE（UTL_TCP.GET_LINE（C，TRUE））; 
 UTL_TCP.CLOSE_CONNECTION（C）; -----关闭SMTP端口连接
 EXCEPTION 
当其他人
 RAISE; 
 END SENDMAIL_ATT;

程序调用：

  declare 
  - 这里的本地变量
 l_clob clob; 
 fileName varchar2（100）; 
 begin 
 
 SELECT file_name，file_content 
 INTO fileName，l_clob 
 FROM my_table 
 WHERE ID = 141; 
 
 SENDMAIL_ATT（MSG_FROM =>'myemail@myDomain.com'，
 MSG_TO =>'myemail@myDomain.com'，
 MSG_SUBJECT =>'测试电子邮件' ，
 MSG_TEXT =>'Some Subject'，
 MSG_ATT => l_clob，
 ATT_FILENAME => fileName）; 
 
 end;

解决方案

我使用这个包发送邮件。它附件可以正常工作，尝试一下：

 创建或更换包装身体SENDMAIL_PKG AS 
 
 PRIORITY_HIGH CONSTANT INTEGER：= 1; 
 PRIORITY_NORMAL CONSTANT INTEGER：= 3; 
 PRIORITY_LOW CONSTANT INTEGER：= 5; 
 
 SMTP_PORT CONSTANT INTEGER：= 25; 
 SMTP_SERVER CONSTANT VARCHAR2（50）：='mailhost'; 
 SMTP_DOMIAN CONSTANT VARCHAR2（50）：= SYS_CONTEXT（'USERENV'，'DB_DOMAIN'）; 
 MIME_BOUNDARY CONSTANT VARCHAR2（50）：='=====边界======'; 
 MIME_MIXED CONSTANT VARCHAR2（50）：='multipart / mixed;'; 
 MIME_TEXT CONSTANT VARCHAR2（50）：='text / plain;'; 
 
 DEFAULT_FROM_NAME CONSTANT VARCHAR2（50）：= SYS_CONTEXT（'USERENV'，'CURRENT_SCHEMA'）; 
 DEFAULT_FROM_MAIL CONSTANT VARCHAR2（50）：= DEFAULT_FROM_NAME ||'@'|| SMTP_DOMIAN; 
 
 
 
 FUNCTION OpenConnection（ToEmail IN VARCHAR2，FromEmail IN VARCHAR2）RETURN UTL_SMTP.connection IS 
 con UTL_SMTP.connection; 
 BEGIN 
  - 设置邮件头
 con：= UTL_SMTP.OPEN_CONNECTION（SMTP_SERVER，SMTP_PORT）; 
 UTL_SMTP.helo（con，SMTP_DOMIAN）; 
 UTL_SMTP.Mail（con，LOWER（FromEmail））; 
 UTL_SMTP.rcpt（con，LOWER（ToEmail））; 
 UTL_SMTP.open_data（con）; 
 RETURN con; 
 END OpenConnection; 
 
 
 PROCEDURE PutHeader（con IN OUT UTL_SMTP.connection，
 Subject IN VARCHAR2，ToEmail IN VARCHAR2，
 FromEmail IN VARCHAR2，FromName IN VARCHAR2，
优先级IN INTEGER）IS 
 BEGIN 
 UTL_SMTP.write_data（con，'From：'|| FromName ||'<'|| LOWER（FromEmail）||'>'|| UTL_TCP .CRLF）; 
 UTL_SMTP.write_data（con，'To：<'|| LOWER（ToEmail）||'>'|| UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，'Subject：'|| Subject || UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，'X-Priority：'|| Priority || UTL_TCP.CRLF）; 
 END PutHeader; 
 
 
程序CloseMail（con IN OUT UTL_SMTP.connection）IS 
 BEGIN 
  - 完成邮件
 UTL_SMTP.close_data（con）; 
 UTL_SMTP.quit（con）; 
 END CloseMail; 
 
  - 在邮件结尾附加默认页脚
 PROCEDURE PutFooter（con IN OUT UTL_SMTP.connection）IS 
发件人VARCHAR2（1000）; 
 BEGIN 
  - 将脚注结束邮件
 UTL_SMTP.write_data（con，UTL_TCP.CRLF || UTL_TCP.CRLF）; 
 sender：= SYS_CONTEXT（'USERENV'，'DB_NAME'）||'。'|| SYS_CONTEXT（'USERENV'，'DB_DOMAIN'）; 
在'| TO_CHAR（LOCALTIMESTAMP，'yyyy-mm-dd hh24：mi：ss'）发送的UTL_SMTP.write_data（con，'来自'|| sender ||'的消息））; 
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 END PutFooter; 
 
 
  - 使用CLOB附件发送邮件
 PROCEDURE SendMail（
主题IN VARCHAR2，
消息IN VARCHAR2，
 ToEmail IN VARCHAR2， 
附件IN CLOB，FilenameAttachment IN VARCHAR2，
 FromEmail IN VARCHAR2 DEFAULT DEFAULT_FROM_MAIL，
 FromName IN VARCHAR2 DEFAULT DEFAULT_FROM_NAME，
优先级IN INTEGER DEFAULT PRIORITY_NORMAL）IS 
 
 con UTL_SMTP.connection; 
 
 AttachmentLength PLS_INTEGER; 
 amount BINARY_INTEGER：= 30000; 
 BUFFER VARCHAR2（32000）; 
 offset PLS_INTEGER：= 1; 
 
 BEGIN 
 
 con：= OpenConnection（ToEmail，FromEmail）; 
 PutHeader（con，Subject，ToEmail，FromEmail，FromName，Priority）; 
 
 UTL_SMTP.write_data（con，'Mime-Version：1.0'|| UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，'Content-Type：'|| MIME_MIXED ||'boundary ='|| MIME_BOUNDARY ||''|| UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，' - '|| MIME_BOUNDARY || UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，'Content-type：text / plain'|| UTL_TCP.CRLF）; 
 
  - 邮件正文
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 UTL_SMTP.write_raw_data（con，UTL_RAW.cast_to_raw（Message））; 
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 PutFooter（con）; 
 
  - 邮件附件
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，' - '|| MIME_BOUNDARY || UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，'Content-Type：'|| MIME_TEXT ||'name ='|| FilenameAttachment ||''|| UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，'Content-Disposition：attachment; filename ='|| FilenameAttachment ||''|| UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 
 AttachmentLength：= DBMS_LOB.GETLENGTH（Attachment）; 
 LOOP 
 EXIT WHEN offset> AttachmentLength; 
 DBMS_LOB.READ（Attachment，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）; 
 UTL_SMTP.write_data（con，' - '|| MIME_BOUNDARY ||' - '|| UTL_TCP.CRLF）; 
 
 CloseMail（con）; 
 
 END SendMail; 
 
 
 
  - 发送无附件的普通邮件
 PROCEDURE SendMail（
主题IN VARCHAR2，
消息IN VARCHAR2，
 ToEmail IN VARCHAR2，
 FromEmail IN VARCHAR2 DEFAULT DEFAULT_FROM_MAIL，
 FromName IN VARCHAR2 DEFAULT DEFAULT_FROM_NAME，
优先级IN INTEGER DEFAULT PRIORITY_NORMAL）IS 
 
 con UTL_SMTP.connection; 
 
 BEGIN 
 
 con：= OpenConnection（ToEmail，FromEmail）; 
 PutHeader（con，Subject，ToEmail，FromEmail，FromName，Priority）; 
 UTL_SMTP.write_data（con，'Content-type：'|| MIME_TEXT ||'charset ='|| GetCharset || UTL_TCP.CRLF）; 
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 UTL_SMTP.write_raw_data（con，UTL_RAW.cast_to_raw（Message））; 
 UTL_SMTP.write_data（con，UTL_TCP.CRLF）; 
 PutFooter（con）; 
 CloseMail（con）; 
 
 END SendMail; 
 
 
 
 END SENDMAIL_PKG;

I have a .txt file in which I have stored my data Semicolon-separated. The file is stored at backend in a Clob.

File:

ABCD;HEAD; 1.0;00204;18.05.2015;00000000;00000000000.00;18.05.2015

I need to send this text file as an attachment via Stored Procedure in Oracle.

Problem:

I get this encoded file attached in the Email:

Procedure:

CREATE OR REPLACE PROCEDURE SENDMAIL_ATT
(
MSG_FROM     VARCHAR2,
MSG_TO       VARCHAR2,
MSG_SUBJECT  VARCHAR2,
MSG_TEXT     VARCHAR2,
MSG_ATT      CLOB,
ATT_FILENAME VARCHAR2
) IS

V_MAILHOST VARCHAR2(50) := 'mail_server';
V_PORT     NUMBER(2) := 25;
V_HELO     VARCHAR2(50) := 'localhost';

C    UTL_TCP.CONNECTION;
RC   INTEGER;
CRLF VARCHAR2(2) := CHR(13) || CHR(10);
MESG VARCHAR2(32767);

V_BUFFER_SIZE CONSTANT PLS_INTEGER := 4000;
V_BUFFER_NEXT PLS_INTEGER := 4000;
V_BODY_BUFFER VARCHAR2(8000 CHAR);
BEGIN

C := UTL_TCP.OPEN_CONNECTION(V_MAILHOST, V_PORT); -- open the SMTP port
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'HELO ' || V_HELO);
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'MAIL FROM: ' || MSG_FROM); ----- MAIL BOX SENDING THE EMAIL
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'RCPT TO: ' || MSG_TO); ----- MAIL BOX RECIEVING   THE EMAIL
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'DATA'); ----- EMAIL MESSAGE BODY START
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C,'Date: ' ||
 TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss'));
  RC := UTL_TCP.WRITE_LINE(C, 'From: ' || MSG_FROM);
RC := UTL_TCP.WRITE_LINE(C, 'MIME-Version: 1.0');
RC := UTL_TCP.WRITE_LINE(C, 'To: ' || MSG_TO);
RC := UTL_TCP.WRITE_LINE(C, 'Subject: ' || MSG_SUBJECT);
RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
RC := UTL_TCP.WRITE_LINE(C, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
RC := UTL_TCP.WRITE_LINE(C); ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/html'); ----- 1ST BODY PART.    EMAIL TEXT MESSAGE
RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer-Encoding: 7bit');
RC := UTL_TCP.WRITE_LINE(C);
RC := UTL_TCP.WRITE_LINE(C, MSG_TEXT); ----- TEXT OF EMAIL MESSAGE
RC := UTL_TCP.WRITE_LINE(C);
RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
RC := UTL_TCP.WRITE_LINE(C, ' name="' || ATT_FILENAME || '"');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer_Encoding: 7bit'); 
RC := UTL_TCP.WRITE_LINE(C, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
RC := UTL_TCP.WRITE_LINE(C, ' filename="' || ATT_FILENAME || '"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
RC := UTL_TCP.WRITE_LINE(C);

--CLOB 
FOR I IN 0 .. FLOOR(DBMS_LOB.GETLENGTH(MSG_ATT) / V_BUFFER_SIZE)
LOOP
DBMS_LOB.READ(MSG_ATT,
V_BUFFER_NEXT,
I * V_BUFFER_SIZE + 1,
V_BODY_BUFFER);

RC := UTL_TCP.WRITE_TEXT(C, V_BODY_BUFFER);

END LOOP;

RC := UTL_TCP.WRITE_LINE(C);
RC := UTL_TCP.WRITE_LINE(C, '.'); ----- EMAIL MESSAGE BODY END
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'QUIT'); ----- ENDS EMAIL TRANSACTION
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
UTL_TCP.CLOSE_CONNECTION(C); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
WHEN OTHERS THEN
RAISE;
END SENDMAIL_ATT;

Procedure Call:

declare
 -- Local variables here
l_clob   clob;
fileName varchar2(100);
begin

SELECT file_name, file_content
INTO fileName, l_clob
FROM my_table
WHERE ID = 141;

SENDMAIL_ATT(MSG_FROM     => 'myemail@myDomain.com',
           MSG_TO       => 'myemail@myDomain.com',
           MSG_SUBJECT  => 'Test Email',
           MSG_TEXT     => 'Some Subject',
           MSG_ATT      => l_clob,
           ATT_FILENAME => fileName);

end;

解决方案

I use this package to send out mails. It works fine with attachments, try it out:

CREATE OR REPLACE PACKAGE BODY SENDMAIL_PKG AS

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

SMTP_PORT               CONSTANT INTEGER := 25;
SMTP_SERVER             CONSTANT VARCHAR2(50) := 'mailhost';
SMTP_DOMIAN             CONSTANT VARCHAR2(50) := SYS_CONTEXT('USERENV', 'DB_DOMAIN');
MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '=====Boundary======';
MIME_MIXED              CONSTANT VARCHAR2(50) := 'multipart/mixed;';
MIME_TEXT               CONSTANT VARCHAR2(50) := 'text/plain;';

DEFAULT_FROM_NAME       CONSTANT VARCHAR2(50) := SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA');
DEFAULT_FROM_MAIL       CONSTANT VARCHAR2(50) := DEFAULT_FROM_NAME||'@'||SMTP_DOMIAN;



FUNCTION OpenConnection(ToEmail IN VARCHAR2, FromEmail IN VARCHAR2) RETURN UTL_SMTP.connection IS
    con UTL_SMTP.connection;
BEGIN
    -- setup mail header
    con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    UTL_SMTP.helo(con, SMTP_DOMIAN);
    UTL_SMTP.Mail(con, LOWER(FromEmail));
    UTL_SMTP.rcpt(con, LOWER(ToEmail));
    UTL_SMTP.open_data(con);
    RETURN con;
END OpenConnection;


PROCEDURE PutHeader(con IN OUT UTL_SMTP.connection, 
    Subject IN VARCHAR2, ToEmail IN VARCHAR2, 
    FromEmail IN VARCHAR2, FromName IN VARCHAR2,
    Priority IN INTEGER) IS
BEGIN
    UTL_SMTP.write_data(con, 'From: "'||FromName||'" <'||LOWER(FromEmail)||'>'||UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'To: <'||LOWER(ToEmail)||'>'||UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Subject: '||Subject||UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'X-Priority: '||Priority||UTL_TCP.CRLF);
END PutHeader;


PROCEDURE CloseMail(con IN OUT UTL_SMTP.connection) IS
BEGIN
    -- finish mail
    UTL_SMTP.close_data(con);
    UTL_SMTP.quit(con);
END CloseMail;

-- Append default footer at the end of mail
PROCEDURE PutFooter(con IN OUT UTL_SMTP.connection) IS
    sender VARCHAR2(1000);
BEGIN
    -- put footer to end of mail
    UTL_SMTP.write_data(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
    sender := SYS_CONTEXT('USERENV', 'DB_NAME')||'.'||SYS_CONTEXT('USERENV', 'DB_DOMAIN');
    UTL_SMTP.write_data(con, 'Message from '||sender||' sent at '||TO_CHAR(LOCALTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss'));   
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
END PutFooter;


-- Send Mail with CLOB attachment
PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN VARCHAR2, 
    ToEmail IN VARCHAR2,
    Attachment IN CLOB, FilenameAttachment IN VARCHAR2,
    FromEmail IN VARCHAR2 DEFAULT DEFAULT_FROM_MAIL,
    FromName IN VARCHAR2 DEFAULT DEFAULT_FROM_NAME,
    Priority IN INTEGER DEFAULT PRIORITY_NORMAL) IS

    con UTL_SMTP.connection;

    AttachmentLength PLS_INTEGER;
    amount BINARY_INTEGER := 30000;
    BUFFER VARCHAR2(32000);
    offset PLS_INTEGER := 1;

BEGIN

    con := OpenConnection(ToEmail, FromEmail);
    PutHeader(con, Subject, ToEmail, FromEmail, FromName, Priority);

    UTL_SMTP.write_data(con, 'Mime-Version: 1.0' || UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Content-Type: '||MIME_MIXED||' boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF); 
    UTL_SMTP.write_data(con, 'Content-type: text/plain' || UTL_TCP.CRLF); 

    -- Mail Body
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(Message));
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    PutFooter(con);

    -- Mail Attachment
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Content-Type: '||MIME_TEXT||' name="'||FilenameAttachment||'"'|| UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Content-Disposition: attachment; filename="'||FilenameAttachment||'"'|| UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);

    AttachmentLength := DBMS_LOB.GETLENGTH(Attachment);
    LOOP
        EXIT WHEN offset > AttachmentLength;
        DBMS_LOB.READ(Attachment, 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);
    UTL_SMTP.write_data(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);

    CloseMail(con);

END SendMail;



-- Send plain Mail without attachment
PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN VARCHAR2, 
    ToEmail IN VARCHAR2,
    FromEmail IN VARCHAR2 DEFAULT DEFAULT_FROM_MAIL,
    FromName IN VARCHAR2 DEFAULT DEFAULT_FROM_NAME,
    Priority IN INTEGER DEFAULT PRIORITY_NORMAL) IS

    con UTL_SMTP.connection;

BEGIN

    con := OpenConnection(ToEmail, FromEmail);
    PutHeader(con, Subject, ToEmail, FromEmail, FromName, Priority);
    UTL_SMTP.write_data(con, 'Content-type: '||MIME_TEXT ||' charset='||GetCharset||UTL_TCP.CRLF );
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(Message));
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    PutFooter(con);
    CloseMail(con);

END SendMail;



END SENDMAIL_PKG;

这篇关于通过Oracle存储过程将电子邮件.txt文件作为附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

通过Oracle存储过程将电子邮件.txt文件作为附件 [英] Email .txt file as an attachment through Oracle Stored Procedure

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

通过Oracle存储过程将电子邮件.txt文件作为附件 [英] Email .txt file as an attachment through Oracle Stored Procedure

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭