1

I want to send excel file with email using PLSQL

I use XLSX_BUILDER_PKG to create excel files and it works fine. It creates excel files correctly.

https://github.com/commi235/xlsx_builder/blob/master/xlsx_builder_pkg.pks

declare
excel BLOB := empty_blob();

begin
  xlsx_builder_pkg.clear_workbook;
  xlsx_builder_pkg.new_sheet;
  xlsx_builder_pkg.cell( 1, 4, 'Start date:', p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ),p_fillId => xlsx_builder_pkg.get_fill('solid','99CCFF'), p_fontId => xlsx_builder_pkg.get_font( 'Calibri',p_bold => true,p_fontsize => 12) );
  xlsx_builder_pkg.cell( 1, 5, sysdate,p_fillId => xlsx_builder_pkg.get_fill('solid','99CC99'),p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ) );
  xlsx_builder_pkg.cell( 4, 4, 'End date:', p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ),p_fillId => xlsx_builder_pkg.get_fill('solid','99CCFF'), p_fontId => xlsx_builder_pkg.get_font( 'Calibri',p_bold => true,p_fontsize => 12) );
  xlsx_builder_pkg.cell( 4, 5, sysdate ,p_fillId => xlsx_builder_pkg.get_fill('solid','99CC99'),p_alignment => xlsx_builder_pkg.get_alignment( p_horizontal => 'center' ));

  xlsx_builder_pkg.save('EXCEL_FILES', 'emp8.xls');
  excel := xlsx_builder_pkg.finish;


 test_send_mail('my@mail.com',
                 'my@mail.com',
                 'Test',
                 'Test',
                 'excel.xls',
                 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                 excel,
                 'myhost',
                 25); 
end;

Then I want to send excel file as attachment, I used xlsx_builder_pkg.finish function to got BLOB and when I send email I've got it with attachment, but excel file is broken.

CREATE OR REPLACE PROCEDURE test_send_mail (p_to          IN VARCHAR2,
                                       p_from        IN VARCHAR2,
                                       p_subject     IN VARCHAR2,
                                       p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                       p_attach_name IN VARCHAR2 DEFAULT NULL,
                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,
                                       p_attach_blob IN BLOB DEFAULT NULL,
                                       p_smtp_host   IN VARCHAR2,
                                       p_smtp_port   IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
  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, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  IF p_attach_name IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
    END LOOP;

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

I've used different MIME Types, but with 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' I've got broken file (hashed information) and with 'application/vnd.ms-excel' I've got file that I couldn't open

mlxquiz
  • 11
  • 1
  • 2
  • Which version of Oracle are you using? If it's 10g or higher you should definitely use the UTL_MAIL package instead of trying to build your own using low-level components. It has procedures specifically for handling attachments. [Find out more](https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_mail.htm#ARPLS384). However, your actual problem seems to be getting the right MIME type. Have a pipe at [this SO thread](https://stackoverflow.com/a/1964182/146325). – APC May 08 '19 at 09:15
  • is `xls`not a binary format and the mime type should be `application/msexcel` and `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` mime type is for a `xlsx` data Format? – hotfix May 08 '19 at 09:37
  • @APC, `UTL_MAIL` supports only `RAW` attachment which has a limit of 2000 bytes. This is not sufficient for Excel files. When I create a new empty Excel sheet then it has already 7.4k – Wernfried Domscheit May 08 '19 at 10:07
  • Maybe compare your code with this one: https://stackoverflow.com/questions/44326026/how-to-export-data-from-log-table-to-email-body-in-oracle/44329605#44329605 – Wernfried Domscheit May 08 '19 at 10:11
  • @WernfriedDomscheit - I thought the limit was 32K (raw or char)? – APC May 08 '19 at 10:11
  • 1
    @APC your are right, I mixed it with the limit of column data type. Anyway, 32k might be not sufficient for Excel files. – Wernfried Domscheit May 08 '19 at 10:18

1 Answers1

1

Comparing this with code that I've got available you seem to be missing the mime header attribute Content-Transfer-Encoding, which should be Base64

UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: Base64' || UTL_TCP.crlf);

Another thing to consider in case that the attachment gets really big, or the code is extended to allow multiple attachments, is to do the base64-encoding before opening the connection to the SMTP-server. Otherwise while converting/writing you might get timed out for keeping the session alive for to long.

Gasparen
  • 81
  • 3