Enviando e-mail pelo Oracle

Novamente, nada de mais aqui pessoal. Só estou deixando aqui pois sempre que preciso enviar e-mail pelo Oracle, tenho que procurar estes comandos por aí.

SQL> SELECT HOST, LOWER_PORT, UPPER_PORT, ACL FROM DBA_NETWORK_ACLS;

no rows selected

SQL> SELECT ACL, PRINCIPAL, PRIVILEGE, IS_GRANT, TO_CHAR(START_DATE, 'DD-MON-YYYY') AS START_DATE, TO_CHAR(END_DATE, 'DD-MON-YYYY') AS END_DATE FROM DBA_NETWORK_ACL_PRIVILEGES ORDER BY START_DATE;

no rows selected

SQL> EXEC DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL=>'mailserver_acl.xml', DESCRIPTION=>'Teste para envio de e-mail.', PRINCIPAL=>'SOE', IS_GRANT=>TRUE, PRIVILEGE=>'connect');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (ACL=>'mailserver_acl.xml', PRINCIPAL=>'SOE', IS_GRANT=>TRUE, PRIVILEGE=>'connect');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL=>'mailserver_acl.xml', PRINCIPAL=>'SOE', IS_GRANT=>TRUE, PRIVILEGE=>'resolve');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (ACL=>'mailserver_acl.xml', HOST=>'*', LOWER_PORT=>NULL, UPPER_PORT=>NULL);

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT HOST, LOWER_PORT, UPPER_PORT, ACL FROM DBA_NETWORK_ACLS;

HOST                           LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------
*                                                    /sys/acls/mailserver_acl.xml

SQL> SELECT ACL, PRINCIPAL, PRIVILEGE, IS_GRANT, TO_CHAR(START_DATE, 'DD-MON-YYYY') AS START_DATE, TO_CHAR(END_DATE, 'DD-MON-YYYY') AS END_DATE FROM DBA_NETWORK_ACL_PRIVILEGES ORDER BY START_DATE;

ACL                            PRINCIPAL                      PRIVILEGE                      IS_GR START_DATE  END_DATE
------------------------------ ------------------------------ ------------------------------ ----- ----------- -----------
/sys/acls/mailserver_acl.xml   SOE                            connect                        true
/sys/acls/mailserver_acl.xml   SOE                            resolve                        true

SQL> CONN SOE/soe@CATALOGO
Connected.
SQL> @CreateProcedure_send_mail.sql

Procedure created.

SQL> @EnviaEmail.sql

PL/SQL procedure successfully completed.

SQL>

Os itens a respeito da configuração de ACL foram retirados de https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.

Estes dois últimos scritps foram copiados e adaptados de http://dicasoracledba.blogspot.com.br/2013/06/send-authenticated-email-using-utlsmtp.html.

Download scripts: http://nervinformatica.com.br/Downloads/CreateProcedure_send_mail.sql e http://nervinformatica.com.br/Downloads/EnviaEmail.sql

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.