Páginas

9 de jan. de 2013

Enviando e-mails com PL/SQL em Bancos de Dados Oracle - Parte 2

Olá pessoal,

     Dando continuidade ao artigo Enviando e-mails com PL/SQL em Bancos de Dados Oracle, irei compartilhar nesta 2a. parte, uma package chamada PKG_ENVIA_EMAIL, que eu desenvolvi para facilitar o trabalho de envio de e-mails com ou sem anexo. Essa package internamente utiliza a package UTL_MAIL para enviar mensagens sem anexo e a package UTL_SMTP para enviar mensagens com anexo do tipo texto. O objetivo de criá-la foi o de padronizar o código de envio de e-mails na empresa em que eu trabalho e facilitar o envio de e-mails com anexos maiores que 32K, pois não é muito fácil utilizar a package UTL_SMTP para essa finalidade (para mais informações sobre este assunto, leia a 1a. parte do artigo).
   
     O package PKG_ENVIA_EMAIL pode ser baixado a partir de um script protegido com senha (para recebê-la assine a newsletter que encontra-se no painel direito deste blog.) e que está disponível para download em arquivo compactado, no link script_pkg_envia_email.zip. O package pode ser instalada e utilizada em qualquer schema de usuário de um SGBD Oracle, desde que sejam atendidos os pré-requisitos especificados na 1a. parte deste artigo.

     O package PKG_ENVIA_EMAIL contém 1 função e 2 stored procedures:

           - FC_GERAR_ARRAY_STRING:
               Função privada que permite gerar array para separar destinatários e enviar o e-mail para múltiplos destinatários.quando necessário.

           - SP_ENVIAR_EMAIL:
               Stored Procedure pública que pode ser utilizada para enviar e-mails sem anexo. Utiliza internamente a package UTL_MAIL.

           - SP_ENVIAR_EMAIL_COM_ANEXO:
               Stored Procedure pública que pode ser utilizada para enviar e-mails COM anexo do tipo texto. Utiliza internamente a package UTL_SMTP e permite enviar somente 1 anexo por e-mail.
           
     Se você precisar mandar outros tipos de anexos, tais como arquivos de vídeo ou som, e/ou precisar mandar mais de 1 arquivo texto anexo, crie nova(s) procedure(s) com sobrecarga dentro da package PKG_ENVIA_EMAIL, incluindo o código necessário para executar essas tarefas. Para isso, você precisará estudar, em detalhes, como utilizar a package UTL_SMTP.

     Para enviar um e-mail sem anexo, execute a stored procedure SP_ENVIAR_EMAIL, como no exemplo abaixo:

   BEGIN
    PKG_ENVIA_EMAIL.SP_ENVIAR_EMAIL (P_ASSUNTO => 'Assunto do e-mail', 
       P_MSG => 'Mensagem', 
       P_EMAIL_ORIGEM => 'oracle@oracle.com', 
       P_EMAIL_DESTINO => 'fbifabio@gmail.com, jack@oracle.com',
       P_EMAIL_CC_DESTINO => 'ze@oracle.com, jo@oracle.com',
       P_EMAIL_CCO_DESTINO => null);
   END;

     Para enviar um e-mail com anexo do tipo texto, execute a stored procedure SP_ENVIAR_EMAIL_COM_ANEXO, como no exemplo abaixo:

   DECLARE
     v_CLOB CLOB;
   BEGIN
    PKG_ENVIA_EMAIL.SP_ENVIAR_EMAIL_COM_ANEXO (P_ASSUNTO => 'Assunto do e-mail',
       P_MSG => 'Mensagem', 
       P_EMAIL_ORIGEM => 'oracle@oracle.com', 
       P_EMAIL_DESTINO => 'fbifabio@gmail.com, jack@oracle.com',     
       P_EMAIL_CC_DESTINO => 'ze@oracle.com, jo@oracle.com', 
       P_EMAIL_CCO_DESTINO => null,
       P_FILENAME => 'arquivo.txt',
       P_ANEXO => v_CLOB,  -- variável CLOB c/ texto do arquivo
       P_ATTACH_MIME => 'text/plain; charset=iso-8859-1', 
       P_SMTP_SERVER => 'smtp.empresa.com.br',
       P_SMTP_PORT => 25);
   END;



Bom pessoal, por hoje é só!


[]s
     

28 comentários:

  1. Prof. Fábio, dá pra testar essa solução naquela VM do curso PL/SQL?

    ResponderExcluir
    Respostas
    1. Antonio, dá sim, desde que a VM esteja em uma rede que contenha um servidor SMTP válido. Antes, leia a parte 1 do artigo, pois existem alguns pré-requisitos que vc deverá seguir.

      []s

      Excluir
  2. Ótimo artigo prof. Fábio.
    Me ajudou muito!
    Obrigado.

    ResponderExcluir
  3. Sou um DBA iniciante e uso muito o material que colocas no teu blog e no GPO e te agradeço muito pelo material que disponibilizas e as verdadeiras aulas que são teus artigos.

    Obrigado!

    ResponderExcluir
    Respostas
    1. Muito obrigado Anônimos (2 últimos comentários)! Nem sempre eu acerto, mas posso garantir que estou sempre mirando no acerto!

      []s

      Excluir
  4. Primeiro parabéns pelo Blog que esta bem escrito e de facil entendimento

    ResponderExcluir
  5. Olá Fábio

    Achei seu artigo no site da oracle.com (http://www.oracle.com/technetwork/pt/articles/sql/enviando-emails-com-pl-sql-1958708-ptb.html).
    Segui todos os passos e não apresentou erros.
    Ao tentar enviar o e-mail esta aparecendo o seguinte erro :
    ERROR at line 1:
    ORA-29279: SMTP permanent error: 501 5.1.7 Bad sender address syntax
    ORA-06512: at "SYS.UTL_MAIL", line 654
    ORA-06512: at "SYS.UTL_MAIL", line 671
    ORA-06512: at line 1

    Pode me ajudar com isso?

    ResponderExcluir
    Respostas
    1. Vc está com problemas na conta do remetente. O endereço dele deve estar inválido.

      Excluir
    2. Fabio

      O endereço do remetente é válido, inclusive como é um teste ainda, coloquei o mesmo endereço de e-mail para o remete e o destinatário.

      Excluir
  6. Fabio

    O endereço do remetente é válido, inclusive como é um teste ainda, coloquei o mesmo endereço de e-mail para o remete e o destinatário.

    ResponderExcluir
    Respostas
    1. Olhe abaixo o que eu encontrei no MOS:

      Cause
      The DNS records that mapped the ip address for the mailserver and mailfilter back to their nodenames had both been removed from the DNS reverse lookup zone.

      Solution
      Add the two DNS records for the mailserver and mailfilter back into the DNS reverse lookup zone.

      []s

      Excluir
  7. Fábio, podes me explicar o que o código a seguir esta fazendo. Imagino que este pedaço do código é que esta anexando o arquivo no email.

    Para anexar o arquivo no email este arquivo deve estar num campo blob?

    FOR I IN 0 .. TRUNC((DBMS_LOB.GETLENGTH(P_ANEXO) - 1 )/L_STEP) LOOP
    UTL_SMTP.WRITE_RAW_DATA(L_MAIL_CONN,UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(P_ANEXO, L_STEP, I * L_STEP + 1)));
    END LOOP;

    No seu código L_step esta inicializado com 24573. Este nr tem algo com o tamanho do arquivo que esta sendo anexado?

    ResponderExcluir
    Respostas
    1. Antonio, o arquivo pode estar em um CLOB ou VARCHAR2, porém já tive alguns problemas com VARCHAR2, por isso uso agora sempre CLOB!

      O número 24573 representa a qtde de caracteres que ele irá escrever por vez dentro do loop. Não sei o porquê deste número exato, pois qdo montei essa procedure peguei esse código de um exemplo que já estava pronto. Como funcionou, nem fui atrás do porquê!

      []s

      Excluir
  8. Olá sr. Fábio.

    Quanto teno executar GRANT EXECUTE ON utl_smtp TO LOGSGA

    Aparece o seguinte Relatório de erro:

    Erro de SQL: ORA-00942: a tabela ou view não existe

    00942. 00000 - "table or view does not exist"

    o sr. sabe o porque? é necessário executar o pacote utl_smtp para isso? Se sim, como faço isso? Obrigado!

    ResponderExcluir
    Respostas
    1. Andresa, o usuário que vc está executando o GRANT tem privilégios de DBA? Se tiver, a package UTL_MAIL não deve estar instalada no seu BD. Leia a 1a. parte deste artigo (tem links no início) para ver como fazer a sua instalação, ok?

      []s

      Excluir
  9. Fabio executei os 2 primeiros scripts porém a UTL_MAIL_INTERNAL está inválida:

    PL/SQL: SQL Statement ignored
    PL/SQL: ORA-00942: table or view does not exist
    PL/SQL: SQL Statement ignored
    PL/SQL: ORA-00942: table or view does not exist

    Faz idéia de como resolver isso?

    ResponderExcluir
    Respostas
    1. Vc instalou a UTL_MAIL, conforme indicado na 1a. parte deste artigo?

      Excluir
  10. Olá Fabio,

    Depois de instalado os pacotes, existe algum script que faça a desinstalação?

    Thiago

    ResponderExcluir
  11. Fabio baixei o arquivo script_pkg_envia_email.zip.
    So que está com senha.

    ResponderExcluir
    Respostas
    1. Adilson, inscreva-se na Newsletter deste blog (ASSINE A NOSSA NEWSLETTER) e ative a inscrição que você irá receber a senha automaticamente (aguarde alguns minutos).

      Excluir
  12. Fabio boa tarde, a senha que recebo no email não esta autenticando
    :Email ou senha inválidos.

    ResponderExcluir
  13. Fabio, bom dia.
    a linha de comando :
    P_SMTP_SERVER VARCHAR2 DEFAULT 'smtp.empresa.com.br', p_smtp_port IN NUMBER DEFAULT 25);

    Eu preciso modificar a informação 'smtp.empresa.com.br'

    ResponderExcluir
    Respostas
    1. Sim, descubra qual é o nome do servidor SMTP da sua empresa e preencha aí.

      Excluir
  14. Olá Fábio, boa tarde.
    O arquivo pede senha para abrir.
    Abraços
    Fabiana

    ResponderExcluir
  15. Pessoal, prestem atenção no post onde falo que vocês devem se cadastrar na newsletter para receber a senha.

    ResponderExcluir