Social Icons

2 de jan. de 2013

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


Olá pessoal,

     O assunto de hoje é a 1ª parte de um artigo (dividido em 2 partes) em que vou compartilhar conhecimentos gerais sobre como enviar e-mails a partir de um SGBD Oracle e mostrarei como evitar os principais problemas que você poderá encontrar tentando realizar esta tarefa. Alguns que eu mesmo passei, outros que alguns leitores do meu blog passaram e me pediram ajuda e outros que vi em fóruns.

 

  Para enviar e-mails a partir de um SGBD Oracle, comumente utilizamos as packages UTL_MAIL e UTL_SMTP, mas é possível utilizar também a package UTL_TCP. Todas elas foram desenvolvidas pela própria Oracle e algumas precisam de instalação adicional. A UTL_SMTP é mais antiga, existe desde o Oracle 8i e já vem instalada, por padrão no BD. A UTL_MAIL está disponível a partir do Oracle 10GR2,  e nada mais é do que um wrapper que utiliza as packages UTL_SMTP e UTL_TCP. Ela foi criada para facilitar o envio de e-mails, mas infelizmente, ela possui algumas restrições.

     Utilizar a package UTL_SMTP para mandar e-mail é bastante complexo e trabalhoso. É bem mais fácil e mais rápido utilizar a package UTL_MAIL, mas como eu havia dito no parágrafo acima, ela possui algumas restrições, tais como: enviar somente 1 anexo e o tamanho do anexo deve ser de no máximo 32K. Estas restrições não existem na package UTL_SMTP, que não vou explicar neste artigo. Na 2a. parte vou compartilhar uma package que usa tanto a UTL_SMTP quanto a UTL_MAIL para enviar e-mails. O código desta package poderá servir como referência para quem pretende ver como usar qualquer uma delas.

     Explicarei abaixo como usar a UTL_MAIL, por ser a opção mais simples e atualmente a mais utilizada. Para utilizá-la é necessário cumprir alguns pré-requisitos:

          1- Instalar a package executando os scripts: $ORACLE_HOME/rdbms/admin/utlmail.sql e $ORACLE_HOME/rdbms/admin/prvtmail.plb. A instalação deve ser efetuada por um usuário que tenha privilégios de DBA;
  
          2-  Configurar o parâmetro de sistema SMTP_OUT_SERVER, com um valor contendo o nome ou IP + porta de um servidor SMTP válido. Se você omitir o endereço da porta, será utilizada como padrão, a porta 25. É importante salientar que a máquina host do BD tenha comunicação com o servidor SMTP, e  também, autorização para enviar mensagens (no software do servidor SMTP não deve existir regras que bloqueiem mensagens da máquina host do BD).
              Ex.: ALTER SYSTEM SET smtp_out_server = 'smtp.empresa.com.br:25';

        Obs.: Para executar o comando acima é necessário ter privilégios de DBA.

          3- O remetente (sender) do e-mail deve ser uma conta de e-mail que não requer autenticação para envio de mensagens.

     Após cumprir os pré-requisitos acima, você já poderá utilizar a package UTL_MAIL. Segue abaixo, um bloco PL/SQL simples, que contém comentários sobre o que deve ser passado como valor em cada parâmetro, e que pode ser utilizado para enviar um e-mail sem anexo, utilizando a procedure SEND desta package:

     BEGIN
          UTL_MAIL.SEND
               (SENDER => 'email@oracle.com', -- remetente da mensagem
                  RECIPIENTS => 'fbifabio@gmail.com',  -- destinatário da mensagem
                  CC => null -- destinatário copiado na mensagem
                  BCC => null -- destinatário com cópia oculta da  mensagem
                  SUBJECT =>  'Assunto do e-mail'-- assunto da mensagem
                  MESSAGE => 'Mensagem do e-mail'-- mensagem do e-mail
                  MIME_TYPE => 'text/plain; charset=iso-8859-1-- mime type + character set do texto da mensagem
               );
     END;

     Como pode ser observado no bloco PL/SQL acima, é muito fácil enviar uma mensagem sem anexo usando a package UTL_MAIL, mas a coisa complica quando você tem que mandar mensagens com anexo, e fica impossível quando o anexo é maior que 32K. Um detalhe em que muitos profissionais encontram problemas e dificuldades, é a configuração dos caracteres do texto das mensagens e do texto dos anexos, que podem apresentar, em muitos casos, os chamados "caracteres desconfigurados". Para evitar estes caracteres desconfigurados é necessário passar o valor adequado para os parâmetros MIME_TYPE, que indicam o tipo de aplicação e o conjunto de caracteres que será utilizado para compor o texto da mensagem ou do anexo Eu normalmente configuro o valor  'text/plain; charset=iso-8859-1e isso já é suficiente para resolver os meus problemas. Se para você esta configuração apresentar caracteres desconfigurados, troque o valor iso-8859-1 por utf-8 ou us-ascii (valor padrão). Para aqueles que ainda assim tiverem problemas e precisarem de mais informações sobre mime types, sugiro a leitura do artigo http://en.wikipedia.org/wiki/MIME.

     Se você tentar mandar um e-mail em um BD 11G, possivelmente você irá se deparar com erro ORA-24247: acesso à rede negado pela ACL (access control list), pois nesta versão do Oracle, para aumentar a segurança do BD, foi acrescentado um controle de acesso mais rígido às packages UTL_MAIL, UTL_SMTP e outras, em que é necessário conceder privilégios especiais ao usuários que irão executá-las. Não iremos entrar em detalhes sobre ACL neste artigo, mas para conceder estes privilégios a um usuário chamado FABIO, por exemplo, execute os blocos PL/SQL abaixo:

begin
   dbms_network_acl_admin.create_acl (
        acl => '
grant_acl.xml',
        description => '
Permite enviar e-mail e usar outras packages',
        principal => '
FABIO', -- observe que o nome do usuário deve estar sempre em UPPERCASE 
        is_grant => TRUE,
        privilege => 'connect
-- este privilégio concedido é que permite que o usuário envie email através do servidor que será especificado no próximo bloco que chama a SP "assign_acl"
        );
  commit;
end; /


begin 
   dbms_network_acl_admin.assign_acl( 
        acl => 'grant_acl.xml',
        host => 'hostname' -- preencha aqui o nome do host do servidor SMTP
        );
   commit;
end; 

 /

     Na próxima parte deste artigo, compartilharei com vocês, uma package 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. O objetivo de criá-la foi para 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.

   
Por hoje é só! Aguardem a 2a. parte!


A 2a. parte deste artigo já foi publicada AQUI


[]s
   

28 comments:

  1. Quero treinamento de PL\SQL para SQL Server é possivel??

    ResponderExcluir
    Respostas
    1. Tchaiser, PL/SQL não existe em SQL Server. A linguagem correspondente em SQL Server chama-se Transact SQL. Infelizmente não sou especialista em SQL Server, e por isso, não ministro treinamentos neste SGBD.

      []s

      Excluir
    2. Tchaiser, nesse site você irá encontrar um ótimo material para PL/SQL. Recomendo.

      www.aprendavirtual.ninehub.com

      Excluir
    3. Tem as minhas vídeoaulas também que são mais caras, mas meu foco não é quantidade e sim qualidade e suporte! Todo aluno meu tem suporte que respondo em média em até 1 dia útil e qdo o aluno ainda não consegue entender por email eu acabo tirando a dúvida por telefone.

      []s

      Excluir
  2. Muito boa dica Fabio, estou ansioso para a segunda parte !

    Uma dúvida que ficou, a pkg UTL_SMTP possibilita anexos maiores? e ou o envio de mais de um anexo?

    ResponderExcluir
    Respostas
    1. Vinicius, obrigado pelo comentário.

      Quanto à sua resposta, sim, a UTL_SMTP possibilita anexos maiores que 32K e também possibilita enviar mais de 1 anexo. A package que eu desenvolvi e irei compartilhar na 2a. parte do artigo usará a UTL_SMTP para enviar anexos maiores que 32K, mas não contemplará múltiplos anexos, oK?

      []s

      Excluir
  3. Oi Fábio, me surgiu uma dúvida, tenho o banco 11g R2 instalado no linux e estou testando o envio de email via as tuas dicas, só que não entendi direito como posso fazer para rodar os scripts utlmail.sql e prvtmail.plb, em minha instalação eles estão em /opt/oracle/product/11.2.0/db/rdbms/admin/. Como faço para rodar os mesmos, existe uma forma de eu rodar acessando via o Oracle SQL Developer, PL/SQL developer, Toad ou somente é possível via sqlplus diretamente no servidor? Outra coisa, caso eu execute estes scripts como usuário sys (SYSDBA), como poderei acessar, tipo rodar o script abaixo via um usuário comum do banco:

    BEGIN
    UTL_MAIL.SEND
    (SENDER => 'email@oracle.com', -- remetente da mensagem
    RECIPIENTS => 'fbifabio@gmail.com', -- destinatário da mensagem
    CC => null, -- destinatário copiado na mensagem
    BCC => null, -- destinatário com cópia oculta da mensagem
    SUBJECT => 'Assunto do e-mail', -- assunto da mensagem
    MESSAGE => 'Mensagem do e-mail', -- mensagem do e-mail
    MIME_TYPE => 'text/plain; charset=iso-8859-1' -- mime type do texto da mensagem
    );
    END;

    Att,

    ResponderExcluir
  4. Douglas, vc precisará executar os scripts diretamente no servidor ou copiá-los para a sua máquina, aí sim vc poderá executá-los no SQL Developer ou qq outra ferramenta.

    Qto ao executar o código com usuário comum que não tenha privilégios de DBA ou SYSDBA, é necessário somente executar 2 passos:

    1- permitir que o usuario execute a package, executando o comando abaixo logado como SYS ou usuario q tenha privilégios de DBA:
    GRANT EXECUTE ON UTL_MAIL TO USUARIO;

    2- atribuir os privilégios de ACL que mencionei no artigo (logado como SYS ou usuario q tbém tenha privs de DBA), ok?

    []s

    ResponderExcluir
  5. Fábio, ao rodar o script abaixo como usuário sys:
    begin
    dbms_network_acl_admin.create_acl (
    acl => 'grant_acl',
    description => 'Permite enviar e-mail e usar outras packages',
    principal => 'AFBWEBP1',
    is_grant => TRUE,
    privilege => 'connect'
    );
    commit;
    end;

    Me é retornado o seguinte erro:
    ORA-46059:Identificador ACL inválido especificado

    Saberias me dizer o que pode estar acontecendo?

    ResponderExcluir
    Respostas
    1. Tinha um erro no artigo, que eu já corrigi!

      Todo nome de ACL deve conter o sufixo .xml, portanto, substitua 'grant_acl' por 'grant_acl.xml', OK?

      Excluir
  6. Ok Fábio, rodei os comandos acima sem problemas, mas ao rodar o script:
    BEGIN
    UTL_MAIL.SEND
    (SENDER => 'email@oracle.com', -- remetente da mensagem
    RECIPIENTS => 'douglascar@gmail.com', -- destinatário da mensagem
    CC => null, -- destinatário copiado na mensagem
    BCC => null, -- destinatário com cópia oculta da mensagem
    SUBJECT => 'Assunto do e-mail', -- assunto da mensagem
    MESSAGE => 'Mensagem do e-mail', -- mensagem do e-mail
    MIME_TYPE => 'text/plain; charset=iso-8859-1' -- mime type do texto da mensagem
    );
    END;

    Me foi retornado o erro de ACL:
    ORA-24247: acesso à rede negado pela ACL (access control list)

    ResponderExcluir
    Respostas
    1. Eu acho que vc errou ao executar o bloco abaixo:

      begin
      dbms_network_acl_admin.assign_acl(
      acl => 'grant_acl.xml',
      host => 'hostname' -- preencha aqui o nome do host do servidor SMTP
      );
      commit;
      end;
      /

      Substitua hostname pelo nome ou IP do host do servidor SMTP, OK? Acrescentei comentários em laranja no artigo para ficar mais fácil de entender!

      []s

      Excluir
  7. Fábio, executei novamente os passos e o erro estava no passo 2, onde havia colocado errado o comando: ALTER SYSTEM SET smtp_out_server = 'smtp.empresa.com.br:25';

    Acertei o mesmo e funcionou. Fiz o teste da segunda parte que é o envio do email com anexo e utilizei o seguinte script para testar o envio com anexo:

    DECLARE
    V_CLOB CLOB;
    BEGIN
    V_CLOB := 'Teste de envio de email';
    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;

    Obrigado pela ajuda Fábio.

    ResponderExcluir
  8. Fábio, bom dia:

    Seria legal informar (principalmente para os iniciantes como eu em BD)que em alguns casos o comando para configurar o smtp_out_server deve vir seguido do arquivo de parâmetro do servidor.

    Eu não estava conseguindo e após pesquisar e inserir este complemento, o sistema foi alterado.

    ALTER SYSTEM SET smtp_out_server = 'smtp.empresa.com.br:25' scope:spfile;

    Abs,

    ResponderExcluir
  9. Boa tarde Fabio, tenho uma dúvida que é a seguinte, como enviar varios emails na mesma execução da procedure, por exemplo que nem no outlook o separador é ";" (ponto e virgula) que ficaria assim ao executar a procedure recipients => email01@servidor.com.br;email01@servidor.com.br;. Porém o mesmo não funcionou o erro da mensagem diz que não existe este e-mail (claro tentei com outros emails este é apenas um exemplo), acredito que ele não esteja interpretando o ";" (ponto-virgula), como um separador de emails e que adicione mais de 1 e-mail para cada ";" (ponto-virgula) que encontra. Obrigado desde já.

    ResponderExcluir
    Respostas
    1. Leandro, na package que eu disponibilizei na 2a. parte deste artigo, utilize caractere "," ao invés de ";", ok?

      Excluir
  10. Boa Tarde Fabio,

    estou utilizando o comando utl_smtp para enviar um html, a imagem do html é recebida porém a acentuação não funciona, mesmo utilizando na montagem do html a tabela de acentos e caracteres do html. Tem alguma dica? obrigada

    ResponderExcluir
    Respostas
    1. Boa tarde Sil, veja na 2a parte deste artigo o parâmetro P_ATTACH_MIME da procedure PKG_ENVIA_EMAIL.SP_ENVIAR_EMAIL_COM_ANEXO. Vc tem que passar o valor correto para ele não desconfigurar os caracteres. Dentro desta procedure vc pode ver o código que utiliza a UTL_MAIL e extrair o que vc precisa para corrigir o problema.

      []s

      Excluir
  11. Oi Fabio, descompactei a package PKG_ENVIA_EMAIL para comparar com o desenvolvimento que fiz, alterar o parametro para 'text/plain; charset=iso-8859-1' fez com que recebesse o texto do html no corpo do email. Tenho utilizado: utl_smtp.write_data(mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' ||utl_tcp.crlf || utl_tcp.crlf);
    Desta forma recebo o email com a imagem gerada pelo html, o problema é a desconfiguração dos acentos. Onde está escrito charset= "iso-8859-1" ja testei charset = 'utf-8' mas sem êxito.

    ResponderExcluir
    Respostas
    1. Teste outras configurações. Veja o que falo sobre mime types aqui neste artigo.

      []s

      Excluir
  12. Boa Tarde Fabio,
    Fiz uns testes, utilizei um codigo pronto do link abaixo, mas no lugar dos acentos recebo o caracter '?' :
    http://do-while-true.blogspot.com.br/2012/02/sending-utf-8-html-email-using-utlsmtp.html

    Encontrei também a descrição do que está ocorrendo no link abaixo:
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_smtp.htm#i1004292

    Na parte que menciona: Text (VARCHAR2) data sent using WRITE_DATA is converted to US7ASCII before it is sent. If the text contains multibyte characters, each multibyte character in the text that cannot be converted to US7ASCII is replaced by a '?' character. If 8BITMIME extension is negotiated with the SMTP server using the EHLO subprogram, multibyte VARCHAR2 data can be sent by first converting the text to RAW using the UTL_RAW package, and then sending the RAW data using WRITE_RAW_DATA.

    Continuo nas tentativas, caso tenha alguma dica me envie por favor.
    Obrigada

    ResponderExcluir
  13. Oi Fabio,
    Deu certo agora!!!

    Fiz o seguinte: declarei o charset como US7ASCII
    e utilizei :
    UTL_SMTP.WRITE_RAW_DATA(conn,
    UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message)));

    Vou passar o codigo completo, para se achar interessante publicar.

    Muito obrigada pela ajuda e atenção!

    ResponderExcluir
  14. Olá Fabio,

    Sabe me informar se existe algum script que faça a desinstalação do UTL_MAIL?

    ResponderExcluir
    Respostas
    1. Não conheço nenhum script, mas é muito simples fazer isso. Basta apagar a package UTL_MAIL, que existe no schema SYS. CUIDADO ao mexer neste schema! Se vc apagar ou alterar algo erroneamente, vc poderá ter sérios problemas no seu BD.

      []s

      Excluir
  15. Bom dia Fabio,
    Estamos migrando os emails para o office 365 e em um teste simples, está sendo apresentando a mensagem ORA-29279: erro permanente de SMTP: 530 5.7.57 SMTP: Client was not authenticated to send anonymous mail during MAIL FROM.
    Estou usando o UTL_MAIL e preciso usar ele, pois todas as aplicações estão amarradas nele.
    Apliquei a alteração do smtp_out_server ALTER SYSTEM SET smtp_out_server = 'smtp.office365.com:25';
    Fiz testes tanto com o nome ou o ip e mesmo assim apresenta falha.
    Existe algo diferente para o Office 365?

    Abraço.

    ResponderExcluir
    Respostas
    1. Bom dia Cristiano,

      A conta que você está utilizando para enviar mensagens tem que ter uma configuração especial para enviar mensagens sem autenticação. Verifique como fazer isso no seu gerenciador de e-mail.

      []s

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)