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_SMTPdisponí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,Por t);
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çolegatti@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,Por t);
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:
SOCOTT@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\s erver\RDBMS\ADMIN\ut lmail.sql;
Pacote criado.
Sinônimo criado.
SYS@XE> @C:\oraclexe\app\oracle\product\10.2.0\s erver\RDBMS\ADMIN\pr vtmail.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 17
ORA-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.
e quando o servidor requer autenticação?
ResponderExcluir