Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


segunda-feira, 5 de abril de 2010

Um pouco dos pacotes UTL_SMTP e UTL_MAIL para uso em envio de e-mails através do servidor Oracle ...

Por Eduardo Legatti

Olá,

Neste artigo irei fazer uma breve demonstração através de exemplos práticos, sobre o uso dos pacotes UTL_SMTP disponível à partir do Oracle 8i e do pacote UTL_MAIL disponível à partir do Oracle 10g R2. Para quem não sabe, estes pacotes possuem algumas procedures úteis para envio de e-mails à partir do próprio banco de dados Oracle. O motivo de eu estar escrevendo sobre este assunto partiu de uma questão postada aqui mesmo no blog, na qual o leitor pergunta como o mesmo faria através de uma trigger de banco de dados, enviar um e-mail informando que uma instrução SQL foi executada em uma tabela qualquer no banco de dados.

Bom, apesar de neste caso o uso do recurso de auditoria ser mais apropriado, eu irei demonstrar através de um exemplo simples, como o DBA poderia receber em seu e-mail uma mensagem enviada pelo banco de dados. Imagine que o DBA está saindo de férias e que o mesmo gostaria de receber em seu e-mail uma mensagem toda vez que uma tabela (de suma importância para ele) fosse alterada através de comandos DML (insert, update, delete).

Para quem utiliza o Oracle 8i ou 9i, o exemplo abaixo funcionará sem problemas, pois utilizarei recursos do pacote UTL_SMTP disponíveis à partir do Oracle 8i.

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Seg Abr 5 08:46:19 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SYS@XE> create user scott identified by tiger default tablespace users;

Usuário criado.

SYS@XE> grant connect,resource to scott;

Concessão bem-sucedida.

SYS@XE> grant execute on UTL_SMTP to scott;

Concessão bem-sucedida.

Após a criação do usuário SCOTT acima, irei executar o bloco PL/SQL abaixo apenas para demonstrar a utilização de alguns procedimentos do pacote UTL_SMTP. No caso, estarei utilizando o servidor SMTP em questão que possui o IP 192.168.1.10 utilizando a porta padrão 25:

SYS@XE> connect scott/tiger
Conectado.

SCOTT@XE> Declare
  2    SendorAddress   varchar2(30) := 'DBA@Server.com';
  3    ReceiverAddress varchar2(30) := 'legatti@intra.rps.com.br';
  4    EmailServer     varchar2(30) := '192.168.1.10';
  5    Port            number  := 25;
  6    conn UTL_SMTP.CONNECTION;
  7    crlf VARCHAR2(2):= CHR(13) || CHR(10);
  8    mesg VARCHAR2(4000);
  9  BEGIN
 10    conn:= utl_smtp.open_connection(EmailServer,Port);
 11    utl_smtp.helo(conn,EmailServer);
 12    utl_smtp.mail(conn,SendorAddress);
 13    utl_smtp.rcpt(conn,ReceiverAddress);
 14    mesg:=
 15           'From:'||SendorAddress|| crlf ||
 16           'Subject: Teste de e-mail enviado pelo Servidor Oracle' || crlf ||
 17           'To: '||ReceiverAddress || crlf ||
 18           '' || crlf ||
 19           'Este e-mail foi enviado pelo servidor de banco de dados Oracle';
 20    utl_smtp.data(conn,mesg);
 21    utl_smtp.quit(conn);
 22  END;
 23  /

Procedimento PL/SQL concluído com sucesso.


Após a execução do bloco PL/SQL acima, poderemos ver na figura abaixo, que a mensagem foi enviada com sucesso para o endereço legatti@intra.rps.com.br.

 

Agora irei criar uma stored procedure que receberá dois parâmetros de entrada: O primeiro será o assunto do e-mail e o segundo será o corpo da mensagem.

SCOTT@XE> create or replace procedure send_email (subject varchar, message varchar) as
  2  BEGIN
  3   Declare
  4     SendorAddress   varchar2(30) := 'DBA@Server.com';
  5     ReceiverAddress varchar2(30) := 'legatti@intra.rps.com.br';
  6     EmailServer     varchar2(30) := '192.168.1.10';
  7     Port            number  := 25;
  8     conn UTL_SMTP.CONNECTION;
  9     crlf VARCHAR2(2):= CHR(13) || CHR(10);
 10     mesg VARCHAR2(4000);
 11   BEGIN
 12     conn:= utl_smtp.open_connection(EmailServer,Port);
 13     utl_smtp.helo(conn,EmailServer);
 14     utl_smtp.mail(conn,SendorAddress);
 15     utl_smtp.rcpt(conn,ReceiverAddress);
 16     mesg:=
 17            'From:'||SendorAddress|| crlf ||
 18            'Subject: '||subject|| crlf ||
 19            'To: '||ReceiverAddress || crlf ||
 20            '' || crlf || message;
 21     utl_smtp.data(conn,mesg);
 22     utl_smtp.quit(conn);
 23   END;
 24  END;
 25  /

Procedimento criado.
  

  
Com a stored procedure criada, irei criar uma tabela de TESTE e uma trigger de banco de dados que será disparada toda vez que uma instrução DML afetar a tabela: 
 
SCOTT@XE> create table teste (id number);

Tabela criada.

SCOTT@XE> create or replace trigger trg_teste_envia_email
  2  after delete or insert or update
  3  on teste
  4  begin
  5    if (inserting) then
  6     send_email('INSERT realizado na tabela TESTE',
  7                'O usuario '||user||' inseriu registros na tabela TESTE');
  8    elsif (deleting) then
  9     send_email('DELETE realizado na tabela TESTE',
 10                'O usuario '||user||' deletou registros da tabela TESTE');
 11    elsif (updating) then
 12     send_email('UPDATE realizado na tabela TESTE',
 13                'O usuario '||user||' atualizou registros na tabela TESTE');
 14    end if;
 15  end;
 16  /

Gatilho criado.


Vale a pena salientar que na criação da trigger de banco de dados acima, eu não usei a cláusula "FOR EACH ROW" na definição da mesma, pois eu não quero receber 100 e-mails caso 100 registros da tabela sejam alterados de uma só vez por uma única instrução DML. O objetivo aqui é receber um e-mail informando que uma operação DML foi executada na tabela, independente de quantos registros foram manipulados.

  
SCOTT@XE> insert into teste values (1);
insert into teste values (1)
* 
ERRO na linha 1:
ORA-29278: erro transiente de SMTP: 421 Service not available 
ORA-06512: em "SYS.UTL_SMTP", line 21
ORA-06512: em "SYS.UTL_SMTP", line 97
ORA-06512: em "SYS.UTL_SMTP", line 139
ORA-06512: em "SCOTT.SEND_EMAIL", line 13
ORA-06512: em "SCOTT.TRG_ENVIA_EMAIL", line 3
ORA-04088: erro durante a execução do gatilho 'SCOTT.TRG_ENVIA_EMAIL'

SCOTT@XE> select * from teste;

não há linhas selecionadas


O erro acima foi gerado de forma propositada, pois eu desliguei o servidor SMTP apenas para demonstrar que caso o mesmo fique indisponível, a transação falhará e, portanto, o ideal seria tratar este tipo de exceção de forma a não comprometer a aplicação e os usuários que manipulam a tabela em questão. Bom, após o restabelecimento do serviço SMTP, vamos aos exemplos abaixo:

 
SCOTT@XE> insert into teste values (1);

1 linha criada.

SOCOTT@XE> delete from teste;

1 linha deletada.

SOCOTT@XE> commit;

Commit concluído.


Após a execução das instruções DML acima, podemos ver na figura abaixo que as mensagens foram enviadas sem maiores problemas. Vale a pena salientar que as mensagens serão enviadas independente da transação ser bem sucedida (COMMIT) ou não (ROLLBACK).



Para quem utiliza o Oracle 10g R2, seria bom dar uma olhada no pacote UTL_MAIL, pois o mesmo possui mais funções além de ser mais simples. Para utilizá-lo, caso o mesmo não esteja instalado, bastará apenas executar os scripts $ORACLE_HOME/rdbms/admin/utlmail.sql e $ORACLE_HOME/rdbms/admin/prvtmail.plb como demonstrado abaixo: 

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Seg Abr 5 08:55:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SYS@XE> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utlmail.sql;

Pacote criado.

Sinônimo criado.

SYS@XE> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\prvtmail.plb;

Corpo de Pacote criado.

Não há erros.


Como complemento, o parâmetro SMTP_OUT_SERVER deverá ser setado afim de armazenar o nome ou endereço IP do servidor SMTP em questão:


SYS@XE> alter system set smtp_out_server = '192.168.1.10:25' scope=both;

Sistema alterado.


Após a configuração acima, poderemos ver abaixo um exemplo de seu uso:

SYS@XE> BEGIN
  2    UTL_MAIL.send(sender => 'DBA@Server.com',
  3    recipients => 'legatti@intra.rps.com.br',
  4    cc         => 'syschangeoninstall@gmail.com',
  5    subject    => 'Teste de envio de e-mail pelo UTL_MAIL',
  6    message    => 'O servidor Oracle me enviou este e-mail utilizando o pacote UTL_MAIL');
  7  END;
  8  /

Procedimento PL/SQL concluído com sucesso.

Após a execução do procedimento acima, podemos ver abaixo que a mensagem foi enviada com sucesso.


Por fim, para quem utilizar o Oracle 11g, poderá se deparar com o erro abaixo:

ERRO na linha 1:
ORA-24247: acesso à rede negado pela ACL (access control list)
ORA-06512: em "SYS.UTL_TCP", line 17ORA-06512: em "SYS.UTL_TCP", line 246
ORA-06512: em "SYS.UTL_SMTP", line 115
ORA-06512: em "SYS.UTL_SMTP", line 138
ORA-06512: em line 11


Neste caso, o Oracle 11g possui uma nova camada de segurança (controle de acesso fino) responsável por permitir ou não, que os usuários de banco de dados façam uso de recursos de rede externo ao servidor de banco de dados através de pacotes como o próprio UTL_MAIL, UTL_SMTP, UTL_HTTP e UTL_TCP, mas não irei entrar em maiores detalhes sobre o ACL pelo fato de não ser o objetivo deste artigo. Portanto, no Oracle 11g, para que seja possível a utilização destes recursos, será necessário a configuração de algumas políticas de acesso (ACL) como demonstrado abaixo:

C:\>sqlplus sys/******* as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Seg Abr 5 08:56:38 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORACLE11> begin
  2    dbms_network_acl_admin.create_acl (
  3      acl         => 'utl_mail.xml',
  4      description => 'Permite enviar e-mail',
  5      principal   => 'SCOTT',
  6      is_grant    => TRUE,
  7      privilege   => 'connect'
  8      );
  9      commit;
 10  end;
 11  /

Procedimento PL/SQL concluído com sucesso.

SYS@ORACLE11> begin
  2    dbms_network_acl_admin.add_privilege (
  3    acl       => 'utl_mail.xml',
  4    principal => 'SCOTT',
  5    is_grant  => TRUE,
  6    privilege => 'resolve'
  7    );
  8    commit;
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

SYS@ORACLE11> begin
  2    dbms_network_acl_admin.assign_acl(
  3    acl  => 'utl_mail.xml',
  4    host => '192.168.1.10'
  5    );
  6    commit;
  7  end;
  8  /

Procedimento PL/SQL concluído com sucesso.


Após a execução dos procedimentos acima, o usuário SCOTT terá os privilégios necessários para a execução dos procedimentos existentes no pacote UTL_MAIL, afim de utilizar o serviço SMTP do servidor em questão.

Google+

11 comentários:

David Ricardo disse...

Olá Eduardo, meus parabéns pelo Post, muito bom e didático, eu já usei essa feature, e posso assegurar que funciona muito bem, e serve para vários propósitos, desde uma simples mensagem de monitoração de banco até controles de alertas. Mais uma vez meus parabéns, ótimo Post.

Eduardo Legatti disse...

Olá David,

Tudo bem? obrigado pelo seu comentário. Realmente, eu diria que essa feature é uma "mão na roda" ;-)

Abraços e até mais ...

Anônimo disse...

Boa tarde Eduardo, parabéns pelo post!!! Estou com uma dúvida de como proceder para enviar no corpo da mensagem o resultado de uma comando sql.
Abraços!!

Eduardo Legatti disse...

Olá Anônimo,

Esta código já seria um pouco mais complexo, mas acredito que se você criar um "CURSOR" e realizar um "LOOP" que armazene as linhas retornadas por ele concatenando-as na variável responsável por armazenar o corpo da mensagem, talvez funcione. Talvez seja necessário utilizar a procedure DBMS_OUTPUT.PUT_LINE para isso. Uma outra alternativa seria salvar o resultado em um arquivo texto e então anexá-lo ao e-mail, mas aí a programação seria bem mais avançada. Para maiores detalhes, acesse a documentação oficial. No mais, acredito que no site do Tom Kyte já tenha alguma solução para isso. Outr artigo que possa vir a ser útil seria este aqui.

Abraços e até mais ...

Vitor Leandro disse...

Olá Eduardo,


Mais uma vez, excelente post. Estava a procura de uma solução como esta.

Ras disse...

Olá Eduardo, muito bom seu post. Eu utilizo o UTL_SMTP há muito tempo para envio de email, mas agora surgiu a necessidade de enviar arquivos anexo, como isso só é possível no UTL_MAIL terei que instalar, só que meu banco é o 9i, será que vai funcionar?

Eduardo Legatti disse...

Olá Ras,

Realmente o suporte à anexos no email veio à partir do Oracle 10g com o UTL_MAIL. No Oracle 9i com o UTM_SMTP você pode tentar alguns procedimentos como demonstrado no link abaixo: Não testei ;-) Verifique se resolve o seu problema.

http://media.techtarget.com/searchOracle/downloads/SendingAttachmentsViaUtl_Smtp.txt

Abraços e até mais.

afonso Moreira disse...

Ola Eduardo, vc sempre nmos surpreendendo com suas dicas. Parabens. Usei seu exemplo para enviar email do meu BD, uso o oracle 10gxe, mas quando tento executar a procedure que esta em rdbms/admin/utlsmtp.sql, recebo a mensagem de que a variavel 'tab' nao decalreada, poderia me dar mais um help.
abraços
afonso

Eduardo Legatti disse...

Olá Afonso,

Porque você está tentando executar o script rdbms/admin/utlsmtp.sql? Realmente não faço idéia do que possa estar ocasionando este erro. Você vai executar na ordem abaixo?

$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

O Oracle Express é 10g ou 11g?

Abraços

Legatti

Guilherme disse...

Muito bom Eduardo!

Mas estou com dúvida referente a configuração pra enviar e-mail através da porta 465, usada pelo smtp da google por exemplo. Esta conexão usa SSL. Como devo proceder a configuração em um Oracle 10g (10.0.2.5)?

Abraços

Eduardo Legatti disse...

Olá Guilherme,

Nunca realizei essa configuração, mas acredito que alguém já tenha feito isso. Talvez tenha que criar algum certificado ou algo do tipo. Faça uma pesquisa no Google usando os termos abaixo que possivelmente você irá achar uma solução.


"Sending email using Oracle database and google mail service"

"send emails using UTL_MAIL Oracle package and Google GMAIL SMTP Server"


Abraços,

Legatti

Postagens populares