仅限甲骨文练习者: UTL_ENCODE在11g发送附件邮件时失败

Question

经过多次尝试和错误，我能够发送一封电子邮件与UTL_SMPT。下面的代码，在以前的版本中，我使用了一个原始文件和T_BLOB作为p_attach_file，它发送了一个32K不可读的PDF作为附件。还不错。但是PDF大于32K，而且我也希望能够阅读它，我使用了代码的原始版本。但不起作用。它说UTL_ENCODE在FOR循环中失败了。

错误代码是

下面是我迄今所做工作的完整代码。

**我的代码读BLOB **

CREATE DIRECTORY PDF_DIR as 'c:\doc\';

create table tmpblob(x varchar2(200), y blob);

DECLARE
v_file_loc BFILE;
v_diagram_loc BLOB;
v_diagram_size INTEGER;
BEGIN
  v_file_loc := BFILENAME('PDF_DIR','doc.pdf');
  v_diagram_size := DBMS_LOB.GETLENGTH(v_file_loc);
  DBMS_OUTPUT.PUT_LINE('Pdf size: ' || v_diagram_size);
  DBMS_OUTPUT.PUT_LINE('Inserting Pdf Diagram Row');
  INSERT INTO tmpblob (x,y)
  VALUES (' ', EMPTY_BLOB )
  RETURNING y INTO v_diagram_loc;
  DBMS_OUTPUT.PUT_LINE('Loading Content of PDF ');
  DBMS_LOB.LOADFROMFILE(v_diagram_loc, v_file_loc, v_diagram_size);
  COMMIT;
  EXCEPTION
  WHEN OTHERS
  THEN
  DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
END; 

DECLARE
 -- l_name images.name%TYPE := 'site_logo.gif';
  l_blob tmpblob.y%TYPE;
BEGIN
  SELECT y
  INTO   l_blob
  FROM   tmpblob;

  send_mail(p_to          => 'root@mail.mycorp.com',
            p_from        => 'admin@mycompany.com',
            p_subject     => 'Test Message',
            p_text_msg    => 'This is a test message.',
            p_attach_name => 'doc.pdf',
            p_attach_mime => 'application/pdf',
            p_attach_blob => l_blob,
            p_smtp_host   => '192.168.50.1');
END;

SEND_MAIL过程:源=> 甲骨文基地

CREATE OR REPLACE PROCEDURE 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-Transfer-Encoding: base64' || 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;
/

Answer 1

试着替换你的循环

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;

通过这一条：

OFFSET := 1;
amount := 48; -- must be a whole multiple of 3. I use 48 because it is common to place NEW_LINE each 64 characters.
LobLen := DBMS_LOB.GETLENGTH(p_attach_blob);
LOOP
   EXIT WHEN OFFSET > LobLen;
   DBMS_LOB.READ(p_attach_blob, amount, OFFSET, BUFFER);
   UTL_SMTP.WRITE_RAW_DATA(l_mail_conn, UTL_ENCODE.BASE64_ENCODE(BUFFER));
   OFFSET := OFFSET + amount;
END LOOP;       

这里有一个更高级的过程：https://stackoverflow.com/questions/44326026/how-to-export-data-from-log-table-to-email-body-in-oracle/44329605#44329605