Oracle plsql email attachment file will empty

Question

I have the PLSQL block listed below, the email is sent but the file that should be attached arrives empty in the destination email.

Send an existing file, no ?

What am I doing wrong ?

Grateful

declare
  ds_email_origem_w varchar2(30);
  ds_email_destino_p varchar2(30);
  ds_assunto       varchar2(50);
  p_attach_clob    CLOB;
  p_attach_file    varchar2(50);
  /* envio de email com blobs  */ 
  l_step        PLS_INTEGER  := 4000;--12000; 
  ds_smtp_w varchar2(20) := '10.19.1.xx';--informo o ip correto
  --ds_user_id_w varchar2(50) := 'envio_email';
  --ds_senha_smtp_w varchar2(50) := '@@@@@@@';
  /* Abre conex?o SMTP e HTTP */
  CONEXAO UTL_SMTP.CONNECTION;
  vs_origem char(25) := '[email protected]';
BEGIN
  ds_email_origem_w := '';
  ds_email_destino_p := '[email protected]';
  ds_assunto         := 'teste attach ' || to_char(sysdate,'yymmddhh24mi');
  for r in (select coluna from tabela) --prencher o email de teste
  loop
    p_attach_clob := ' ' || p_attach_clob || r.coluna;
  end loop;


  /* Abre conex?o com um Servidor SMTP(Simple Mail Transfer Protocol), porta padr?o SMTP e 25 */
  CONEXAO := utl_smtp.open_connection (ds_smtp_w,25);
  UTL_SMTP.HELO (CONEXAO, ds_smtp_w); /* Endereco do servidor de SMTP */
  --utl_smtp.command (CONEXAO, 'AUTH LOGIN');
  --utl_smtp.command (CONEXAO, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((ds_user_id_w)))));
  --UTL_SMTP.COMMAND (CONEXAO, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW((DS_SENHA_SMTP_W)))));
  UTL_SMTP.MAIL (CONEXAO, ('<' || vs_origem          || '>')); /* E-mail de quem esta mandando */
  UTL_SMTP.RCPT (CONEXAO, ('<' || ds_email_destino_p || '>')); /* Para quem vou mandar */
  UTL_SMTP.OPEN_DATA(CONEXAO);
  UTL_SMTP.WRITE_DATA(CONEXAO,'Subject'|| ': ' || ds_assunto || utl_tcp.CRLF);

   UTL_SMTP.write_data(
        CONEXAO,
           'Content-Disposition: attachment; filename="'
        || 'lmeu_arquivo.txt'  --arquivo que existe na pasta UTL padrão
        || '"'
        || UTL_TCP.crlf
    );

  --UTL_SMTP.WRITE_DATA(CONEXAO, utl_tcp.CRLF || ds_mensagem_p);
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) 
  LOOP
    UTL_SMTP.WRITE_DATA(CONEXAO, /*utl_tcp.CRLF || */ DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
  END LOOP;
  UTL_SMTP.CLOSE_DATA(CONEXAO);
  UTL_SMTP.QUIT (CONEXAO);
Exception
  when OTHERS then
    utl_smtp.quit (conexao);
    RAISE_APPLICATION_ERROR(-20011,'Nao foi possivel enviar o e-mail devido ao seguinte erro: ' || SQLERRM);
END;

Answer 1

Take this example:

DECLARE
   v_From       VARCHAR2(80) := '[email protected]';
   v_Recipient  VARCHAR2(80) := '[email protected]';
   v_Subject    VARCHAR2(80) := 'test subject';
   v_Mail_Host  VARCHAR2(30) := 'mail.mycompany.com';
   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 ||

    'MIME-Version: 1.0'|| crlf ||   -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    'Content-Transfer_Encoding: 7bit'|| crlf ||
    crlf ||
    'some message text'|| crlf ||   -- Message body
    'more message text'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    ' name="excel.csv"'|| crlf ||
    'Content-Transfer_Encoding: 8bit'|| crlf ||
    'Content-Disposition: attachment;'|| crlf ||
    ' filename="excel.csv"'|| crlf ||
    crlf ||
    'CSV,file,attachement'|| crlf ||    -- Content of attachment
    crlf ||

    '-------SECBOUND--'         -- End MIME mail
  );

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