Como Anexar um PDF em E-mail Via PL/SQL

[Charles2f]

Senhores Boa Tarde,
Estou tentando Enviar um Anexo de PDF em um E-mail Utilizando UTL_SMTP, mas não consigo Anexar o PDF.

Alguns dos Senhores poderiam me ajudar nessa.

Obrigado.

[Trevisolli]

Brother, beleza?

Dá uma procurada aqui no fórum por anexo que irá encontrar exemplos interessantes, como esse abaixo:

http://www.glufke.net/oracle/viewtopic. ... ight=anexo

Espero que o ajude.

[Charles2f]

Trevisolli,

Obrigado,

Mas este Exemplo que você me enviou Ele está lendo o Conteudo do Arquivo e Escrevendo no Corpo do E-mail, não é bem isso que eu preciso.

Prociso anexar um Arquivo em PDF à um E-mail que estou enviando via PL/SQL.

Vou fazer a busca aqui no forum utilizando a palavra chave que você me passou.

Obrigado.

[bertosro]

Caros Colegas Boa Tarde.
Tambem preciso anexar um Arquivo em PDF há um E-mail que estou enviando via PL/SQL.

alguém tem alguma dica?

[Trevisolli]

Pessoal, seguem uns exemplos que encontrei na net:

Send mail with UTL_SMTP - with attachments
--

Selecionar tudo

DECLARE
   v_From       VARCHAR2(80) := 'oracle@mycompany.com';
   v_Recipient  VARCHAR2(80) := 'test@mycompany.com';
   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: '||sqlerrm);
END;
/

--
Send mail with UTL_TCP - with attachments
--

Selecionar tudo

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX SENDING THE EMAIL
  msg_to      varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX RECIEVING THE EMAIL
  msg_subject varchar2 := 'Output file TEST1',          ----- EMAIL SUBJECT
  msg_text    varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
  v_output1   varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT WILL BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
  c  utl_tcp.connection;
  rc integer;
  crlf VARCHAR2(2):= CHR(13)||CHR(10);
  mesg VARCHAR2(32767);
BEGIN
  c := utl_tcp.open_connection('196.35.140.18', 25);       ----- OPEN SMTP PORT CONNECTION
  rc := utl_tcp.write_line(c, 'HELO 196.35.140.18');       ----- PERFORMS HANDSHAKING WITH SMTP SERVER
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18');       ----- PERFORMS HANDSHAKING, INCLUDING EXTRA INFORMATION
  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||' < '||msg_from||'>');
  rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
  rc := utl_tcp.write_line(c, 'To: '||msg_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, <i>);                                ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD</i>
  rc := utl_tcp.write_line(c, '-------SECBOUND');
  rc := utl_tcp.write_line(c, 'Content-Type: text/plain');        ----- 1ST BODY PART. EMAIL TEXT MESSAGE
  rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
  rc := utl_tcp.write_line(c, <i>);</i>
  rc := utl_tcp.write_line(c, msg_text);                          ----- TEXT OF EMAIL MESSAGE
  rc := utl_tcp.write_line(c, <i>);</i>
  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="Test.txt"');
  rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
  rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
  rc := utl_tcp.write_line(c, ' filename="Test.txt"');             ----- SUGGESTED FILE NAME FOR ATTACHMENT
  rc := utl_tcp.write_line(c, <i>);</i>
  rc := utl_tcp.write_line(c, v_output1);
  rc := utl_tcp.write_line(c, '-------SECBOUND--');
  rc := utl_tcp.write_line(c, <i>);</i>
  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_application_error(-20000, SQLERRM);
END;
/

Fonte: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL.

[Aldo Andrade]

Bom dia! Estou como mesmo problema. Você conseguiu resolver?

Estou gerando o arquivo em PDF, porém não estou conseguindo anexar ao e-mail.

Aldo.

[celsend]

Pessoal, segue uma procedure que faz o envio do pdf certinho.
Basicamente alguns ajustes foram feitos.
Por favor trocar os que tiver yyyyy pelo valores corretos e observea explicação ao lado.

abraços e sucesso a quem usar

Selecionar tudo

----
---- não COLOQUE VALORES NOS PARAMETROS MESSAGE E HTML_MESSAGE DEIXE NULO.
create or replace
procedure ANEXAR_PDF_EMAIL(from_name varchar2,
                      to_names varchar2,
                      subject varchar2,
                      message varchar2 default null,
                      html_message varchar2 default null,
                      cc_names varchar2 default null,
                      bcc_names varchar2 default null,
                      filename1 varchar2 default null,
                      filetype1 varchar2 default 'text/plain',
                      filename2 varchar2 default null,
                      filetype2 varchar2 default 'text/plain',
                      filename3 varchar2 default null,
                      filetype3 varchar2 default 'text/plain')
is

   smtp_host          varchar2(256) := 'mail.yyyyy.com.br'; 
   --favor consulte o qual o nome do dominio que faz o serviço smtp. Ex mail.seudominio.com.br 
   smtp_port          number := '999';--pergunte ao seu dominio qual  a porta usada parra envio smtp

   boundary           constant varchar2(256) := 'CES.Boundary.DACA587499938898';

   recipients         varchar2(32767);
   directory_path     varchar2(256) := 'nnnnnnn';
--caso possua UM DIRECTORY CRIADO NO BANCO E SO COLOCAR O NOME AQUI SENAO O CAMINHO DA REDE
   file_name          varchar2(256);
   crlf               varchar2(2):= chr(13) || chr(10);
   mesg               varchar2(32767);
   conn               UTL_SMTP.CONNECTION;
   type varchar2_table is table of varchar2(256) index by binary_integer;
   file_array         varchar2_table;
   type_array         varchar2_table;
   i                  binary_integer;

   FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
   
      addr VARCHAR2(256);
      i    pls_integer;
   
      FUNCTION lookup_unquoted_char(str  IN VARCHAR2,
                                    chrs IN VARCHAR2) RETURN pls_integer IS
         c            VARCHAR2(5);
         i            pls_integer;
         len          pls_integer;
         inside_quote BOOLEAN;

      BEGIN

         inside_quote := false;
         i := 1;
         len := length(str);
         WHILE (i <= len) LOOP
            c := substr(str, i, 1);
            IF (inside_quote) THEN
               IF (c = '"') THEN
                  inside_quote := false;
               ELSIF (c = '\') THEN
                  i := i + 1; -- Skip the quote character
               END IF;
               GOTO next_char;
            END IF;
            IF (c = '"') THEN
               inside_quote := true;
               GOTO next_char;
            END IF;
            IF (instr(chrs, c) >= 1) THEN
               RETURN i;
            END IF;
            <<next_char>>
            i := i + 1;
         END LOOP;
         RETURN 0;
      END;
   
   BEGIN

      addr_list := ltrim(addr_list);
      i := lookup_unquoted_char(addr_list, ',;');
      IF (i >= 1) THEN
         addr := substr(addr_list, 1, i - 1);
         addr_list := substr(addr_list, i + 1);
      ELSE
         addr := addr_list;
         addr_list := '';
      END IF;
      i := lookup_unquoted_char(addr, '<');
      IF (i >= 1) THEN
         addr := substr(addr, i + 1);
         i := instr(addr, '>');
         IF (i >= 1) THEN
            addr := substr(addr, 1, i - 1);
         END IF;
      END IF;
      RETURN addr;
   END;

   PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2,
      file_name OUT VARCHAR2) IS

      pos number;

   begin

      pos := instr(file_path,'/',-1);
      if pos = 0 then
         pos := instr(file_path,'\',-1);
      end if;
      if pos = 0 then
         directory_path := null;
      else
         directory_path := substr(file_path,1,pos - 1);
      end if;
      file_name := substr(file_path,pos + 1);

   end;

   PROCEDURE append_file(directory_path IN VARCHAR2, file_name IN VARCHAR2,
      file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION) IS

      generated_name  varchar2(30) := 'nnnnnn';-- NOME DO DIRECTORY DO BANCO OU CAMINHO DE REDE
      directory_name  varchar2(30) := 'nnnnnn';  -- IDEM AO ANTERIOR
      file_handle     utl_file.file_type;
      bfile_handle    bfile;
      bfile_len       number;
      pos             number;
      read_bytes      number;
      line            varchar2(1000); 
      data            raw(200);
      my_code         number;
      my_errm         varchar2(32767);

   begin

      begin
         if substr(file_type,1,4) != 'text' then
            bfile_handle := bfilename(directory_name,file_name);
            bfile_len := dbms_lob.getlength(bfile_handle);
            pos := 1;
            dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
         else
            file_handle := utl_file.fopen(directory_name,file_name,'r');
         end if;
   
        loop
   
            if substr(file_type,1,4) != 'text' then
               if pos + 57 - 1 > bfile_len then
                  read_bytes := bfile_len - pos + 1;
               else
                  read_bytes := 57;
               end if;
               dbms_lob.read(bfile_handle,read_bytes,pos,data);
               utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));
               pos := pos + 57;
               if pos > bfile_len then
                  exit;
               end if;
   
   
            else
               utl_file.get_line(file_handle,line);
               utl_smtp.write_data(conn,line || crlf);
            end if;
      
         end loop;
  
      exception
         when no_data_found then
            null;
         when others then
            my_code := SQLCODE;
            my_errm := SQLERRM;
            dbms_output.put_line('Error code ' || my_code || ': ' ||
               my_errm);
      end;
      if substr(file_type,1,4) != 'text' then
         dbms_lob.close(bfile_handle);
      else
         utl_file.fclose(file_handle);
      end if;
   end;

begin

   file_array(1) := filename1;
   file_array(2) := filename2;
   file_array(3) := filename3;
   type_array(1) := filetype1;
   type_array(2) := filetype2;
   type_array(3) := filetype3;

---AQUI E PARTE MAIS IMPORTANTE DO PROCESSO E NECESSARIO AUTENTICAR O USUARIO DO DOMINIO 
-- SENDO ASSIM USE UM LOGIN E SENHA VALIDOS NOS VALORES yyyyy

   conn := utl_smtp.open_connection(smtp_host,smtp_port);
   utl_smtp.EHLO(conn,smtp_host);--- UM ERRO CLASSICO AQUI não É HELO MAS SIMM EHLO.
   utl_smtp.command (conn, 'AUTH LOGIN');
   utl_smtp.command (conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(('rrrrrrrrrrrrrrrrrrrrrrrr')))));
   --INFORME LOGIN DO EMAIL DO DOMINNIO EX. aaaaaa@seudominio.com.br
   utl_smtp.command (conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(('rrrrrrrrrr')))));
   --INFORME A SENHA
   recipients := from_name;
   utl_smtp.mail(conn,get_address(recipients));
   recipients := to_names;
   while recipients is not null loop
      utl_smtp.rcpt(conn,get_address(recipients));
   end loop;
   recipients := cc_names;
   while recipients is not null loop
      utl_smtp.rcpt(conn,get_address(recipients));
   end loop;
   recipients := bcc_names;
   while recipients is not null loop
      utl_smtp.rcpt(conn,get_address(recipients));
   end loop;
   utl_smtp.open_data(conn);

   mesg := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf ||
      'From: ' || from_name || crlf ||
      'Subject: ' || subject || crlf ||
      'To: ' || to_names || crlf;
   if cc_names is not null then
      mesg := mesg || 'Cc: ' || cc_names || crlf;
   end if;
   if bcc_names is not null then
      mesg := mesg || 'Bcc: ' || bcc_names || crlf;
   end if;
   mesg := mesg || 'Mime-Version: 1.0' || crlf ||
      'Content-Type: multipart/mixed; boundary="' || boundary || '"' ||
      crlf || crlf ||
      'This is a Mime message, which your current mail reader may not' || crlf ||
      'understand. Parts of the message will appear as text. If the -----remainder' || crlf ||
      'appears as random characters in the message body, instead of as' || crlf ||
      'attachments, then you''ll have to extract these parts and decode them' || crlf ||
      'manually.' || crlf || crlf;
   utl_smtp.write_data(conn,mesg);


   if message is not null then
      mesg := '--' || boundary || crlf ||
         'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' ||
          crlf ||
         'Content-Disposition: inline; filename="message.txt"' || crlf ||
         'Content-Transfer-Encoding: 7bit' || crlf || crlf; 
      utl_smtp.write_data(conn,mesg);
      if substr(message,1,1) = '/' then
         split_path_name(message,directory_path,file_name);
         append_file(directory_path,file_name,'pdf',conn);
         utl_smtp.write_data(conn,crlf);
      else
         utl_smtp.write_data(conn,message || crlf);
      end if;
   end if;


   if html_message is not null then
      mesg := '--' || boundary || crlf ||
         'Content-Type: text/html; name="message.html"; charset=US-ASCII' ||
         crlf ||
         'Content-Disposition: inline; filename="message.html"' || crlf ||
         'Content-Transfer-Encoding: 7bit' || crlf || crlf; 
      utl_smtp.write_data(conn,mesg);
      if substr(html_message,1,1) = '/' then
         split_path_name(html_message,directory_path,file_name);
         append_file(directory_path,file_name,'pdf',conn);
         utl_smtp.write_data(conn,crlf);
      else
         utl_smtp.write_data(conn,html_message || crlf);
      end if;
   end if;

   for i in 1..3 loop 
 --- aqui o loop esta com 3 filenames ou seja anexar ate 3 arquivos se quiser aumente os filenames 
 ----  e contador deste loop

        if file_array(i) is not null then

         split_path_name(file_array(i),directory_path,file_name);

         mesg := crlf || '--' || boundary || crlf;
         if substr(type_array(i),1,4) != 'text' then
            mesg := mesg || 'Content-Type: ' || type_array(i) ||
               '; name="' || file_name || '"' || crlf ||
               'Content-Disposition: attachment; filename="' ||
               file_name || '"' || crlf ||
               'Content-Transfer-Encoding: base64' || crlf || crlf ;
         else
            mesg := mesg || 'Content-Type: application/octet-stream; name="' ||
               file_name || '"' || crlf ||
               'Content-Disposition: attachment; filename="' ||
               file_name || '"' || crlf ||
               'Content-Transfer-Encoding: 7bit' || crlf || crlf ;
         end if;
         utl_smtp.write_data(conn,mesg);
         append_file(directory_path,file_name,type_array(i),conn);
         utl_smtp.write_data(conn,crlf);

      end if;
   end loop;

   mesg := crlf || '--' || boundary || '--' || crlf;
   utl_smtp.write_data(conn,mesg);


   utl_smtp.close_data(conn);
   utl_smtp.quit(conn);
--
end ANEXAR_PDF_EMAIL;