oracle PL/SQL 发送带附件的电子邮件？

PL/SQL send email with attachment?

提问by Data-Base

we have a table with files saved as BLOB

我们有一个文件保存为 BLOB 的表

I write a code that email these files as an attachment!

我写了一个代码，将这些文件作为附件通过电子邮件发送！

everything works fine so far, but the files (EXCEL,PDF, ... what ever) are not readable by the programs, only text files and excel will open but after some error message, where PDFs all not be opened at all!

到目前为止一切正常，但是程序无法读取文件（EXCEL，PDF，......无论如何），只有文本文件和 excel 会打开，但在出现一些错误消息后，PDF 文件根本无法打开！

here is the part of the code in question!

这是有问题的代码部分！

utl_smtp.write_data( l_connection, '--'|| l_boundary || utl_tcp.crlf);
utl_smtp.write_data( l_connection, 'Content-Type: application/octet-stream' || utl_tcp.crlf);
utl_smtp.write_data( l_connection, 'Content-Disposition: attachment; filename="' || V_NAME || '"' || utl_tcp.crlf);
utl_smtp.write_data( l_connection, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
utl_smtp.write_data( l_connection, utl_tcp.crlf );   

v_length := dbms_lob.getlength(V_BLOB_CONTENT);     

while v_offset < v_length loop
       dbms_lob.read( V(i).BLOB_CONTENT, v_buffer_size, v_offset, v_raw );
       utl_smtp.write_raw_data( l_connection, utl_encode.base64_encode(v_raw) );
       utl_smtp.write_data( l_connection, utl_tcp.crlf );
       v_offset := v_offset + v_buffer_size;
end loop while_loop;     

utl_smtp.write_data( l_connection, utl_tcp.crlf );

any suggestions?

有什么建议？

回答by Harrison

here is a procedure that I use to do just that

这是我用来做的一个程序

  PROCEDURE StreamAttachmentToConn( p_conn       IN OUT utl_smtp.connection
                                   ,p_boundary   IN raw
                                   ,p_FileName   IN VARCHAR2
                                   ,p_FileData   IN BLOB) PARALLEL_ENABLE
  AS
      l_len       integer := 0 ;
      l_idx       integer := 1 ;
      l_buff_size integer := 57 ;
      l_raw       raw(57) ;
  BEGIN

              -- Attachment
            utl_smtp.write_data( p_conn, '--' || p_boundary || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, 'Content-Type: application/octet-stream' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, 'Content-Disposition: attachment; ' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, ' filename="' || p_FileName || '"' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, utl_tcp.crlf );
            -- Loop through the blob
            -- chuck it up into 57-byte pieces
            -- and base64 encode it and write it into the mail buffer
            l_len := dbms_lob.getlength(p_FileData);

            -- force reinit on this may change
            l_buff_size := 57 ;
            l_idx := 1;

            while l_idx < l_len loop
              dbms_lob.read( p_FileData , l_buff_size, l_idx, l_raw );

              utl_smtp.write_raw_data( p_conn, utl_encode.base64_encode(l_raw) );

              utl_smtp.write_data( p_conn, utl_tcp.crlf );

              l_idx := l_idx + l_buff_size;
            end loop;
  END StreamAttachmentToConn;

I use this to add multiple attachments to a single email. works like a champ for me.

我使用它向单个电子邮件添加多个附件。对我来说就像冠军一样。

one thing, the p_boundary is passed in as

一件事， p_boundary 作为

l_boundary raw(32) := sys_guid();

l_boundary raw(32) := sys_guid();

I see that you already have an l_boundary and that is what you should use.

我看到您已经有了一个 l_boundary，这就是您应该使用的。

this is based on http://christopherbeck.wordpress.com/category/plsql/as well as http://www.oracle-base.com/articles/misc/EmailFromOraclePLSQL.php#attachment.

这是基于http://christopherbeck.wordpress.com/category/plsql/以及http://www.oracle-base.com/articles/misc/EmailFromOraclePLSQL.php#attachment。

then in your code, just pass in your smtp connection, the l_boundary (ie RAW sys_guid or whatever you are using, the name of the file (as it will appear on the email attachment), and the BLOB.

然后在您的代码中，只需传入您的 smtp 连接、l_boundary（即 RAW sys_guid 或您正在使用的任何文件）、文件名称（它将出现在电子邮件附件中）和 BLOB。

---

---

*EDIT --> additional information *

*编辑 --> 附加信息 *

---

---

Ours are the same with the assumption of:

我们的假设是相同的：

--> assume v_offset starts with 1 --> v_buffer_size assume 57

--> 假设 v_offset 以 1 开头 --> v_buffer_size 假设为 57

ButI have noticed that there is an order that you must follow to get it to work (notably, put the attachments next to the ENDafter body!!!

但我注意到你必须遵循一个命令才能让它工作（特别是，将附件放在正文之后的END旁边！！！

1. Connect LOGIC
2. add all of the recipients
3. Header LOGIC :"FROM", Subject
4. Body logic (body text etc)
5. Attachments
6. then after the attachments close the email (this is specifically what I have after my attachments:

1. 连接逻辑
2. 添加所有收件人
3. 标题逻辑：“从”，主题
4. 正文逻辑（正文等）
5. 附件
6. 然后在附件关闭电子邮件后（这是我在附件之后的具体内容：

---

---

utl_smtp.write_data( l_conn, utl_tcp.crlf );

-- Close Email
utl_smtp.write_data( l_conn, '--' || l_boundary || '--' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf || '.' || utl_tcp.crlf );
utl_smtp.close_data( l_conn );
utl_smtp.quit( l_conn );

回答by tbone

Tim Hall has a great site (oracle-base) that has what you're looking for. Note in particular how the encoding is done.

Tim Hall 有一个很棒的站点 ( oracle-base)，里面有你要找的东西。特别注意编码是如何完成的。

I've used similar code to send emails via pl/sql without problem

我使用类似的代码通过 pl/sql 发送电子邮件没有问题