Comunidad Oracle Hispana

Como enviar emails con Oracle Database 10g, 11g & 12c

USO DE UTL_STMP y UTL_MAIL

Para el envio de emails, Oracle, facilita los APIs PL/SQL UTL_STMP y UTL_MAIL.

La diferencia entre estos dos APIs es la facilidad de uso que permite UTL_MAIL, ya que este paquete, Oracle lo incorporo a partir de la versión 10g, en su funcionalidad interna incorpora mucha de la ingeniería que utiliza el paquete UTL_STMP.

El presente blog tratará acerca enviar email utilizando el paquete UTL_SMTP, para el envio de email utilizando el paquete UTL_MAIL, Referirse al artículo  Send Mail Via Oracle UTL_MAIL .

Pasos para llevar a cabo la instalación y uso del paquete UTL_SMP

Los pasos a seguir son los siguientes:

 

1: Verificar que el servidor de correo este accesible.

2: Verificar que el paquete UTL_SMTP ya este instalado en la base de datos, sino, proceder a instalarlos traves de la ejecución de dos scripts que estan el $ORACLE_HOME/rdbms/admin

3: Otorgar el privilegio de ejecución sobre el paquete UTL_SMTP al usuario que lo requiera

4: Crear las listas de control de acceso (ACL) para que Oracle permita acceso al servidor de correo SMTP.

5: Asociar los nombres o direcciones IP de los servidores SMTP que se desea acceder.

6: Elaborar el componente PL/SQL (Paquete o procedimiento) que se utilizará para el envio de email, ya sea con o sin adjuntos.

7: Una vez instalado el envio de correo por medio del paquete UTL_SMTP, utilizar el motor PL/SQL para envio de correo cuantas veces sea necesario.

En las siguientes secciones se detalla cada uno de los paso antes enumerados.

1. Verificar que el servidor de correos este accesible para la instancia de base de datos oracle

La verificación se puede llevar a cabo dando en la ventana de comandos, un PING al nombre o IP del servidor SMTP, como se muestra a continuación:

En caso, el servidor no este disponible para su acceso, se deben hacer las gestiones con los responsables de la Infraestructura Tecnlogica, a fin que se verifiquen y aseguren las credenciales de identificación del servidor.

2: Verificar que el paquete UTL_SMTP ya este instalado en la base de datos, sino, proceder a instalarlos traves de la ejecución de dos scripts que estan el $ORACLE_HOME/rdbms/admin

2.1 Verificando que exista en la base de datos paquete UTL_SMTP:

La verificación si existe el paquete UTL_SMTP, se puede hacer de las siguientes formas:

2.1.1 Mediante un SELECT del registro del paquete en la tabla del sistema ALL_OBJECTS:

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'UTL_SMTP';

Si el paquete ya existe, el resultado de la consulta será similar al mostrado en la siguiente figura:

Si se recibe el mensaje que no existe registro, y la sintaxis del SELECT esta aseguranda, significa que el paquete no está instalado en la base de datos, y se debe proceder a instalarlo (Referirse a 2.1.3).

2.1.2 Invocando una conexión al servidor utilizando el siguiente bloque:

Con la ejecución del siquiente bloque PL/SQL, se puede verificar la existencia y disponibilidad de los servicios SMTP en la base de datos Oracle.

​ 

DECLARE 

  ---- Definicion de variables para conección a servidor SMTP

    Vmailhost   VARCHAR2(64) := 'mail.server.com';  -- Id o direcci;on IP del servidor SMTP

    Vmail_conn  utl_smtp.connection;

BEGIN

  --- Probando disponibilidad del servidor de correo   

  Vmail_conn := utl_smtp.open_connection (Vmailhost, 25);

END;

Si se recibe el mensaje que el paquete utl_smtp debe ser declardo, significa que el paquete no está instalado en la base de datos, y se debe proceder a instalarlo (Referirse a 2.1.3).

Si el servicio no está disponible se mostrará el error "ORA-29278 Error transitorio de SMTP: 421 Servicio No Disponible", como se muestra a continuación:

En este caso se debe proceder con los pasos del 3 al 5.

2.1.3 Instalando el paquete UTL_SMTP

La instalación y compilación de los scripts requeridos para crear el paquete UTL_SMTP, se debe llevar a cabo con el usuario SYS conectado como SYSDBA. La dirección, $ORACLE_HOME, se refiere a la utilizanda por el servidor de la base de datos, no a la utilizada por el cliente. 

      SQLPLUS / AS SYSDBA

     SQL>@$ORACLE_HOME/rdbms/admin/utlsmtp.sql

     SQL>@$ORACLE_HOME/rdbms/admin/prvtsmtp.plb

:

3: Otorgar el privilegio de ejecución sobre el paquete UTL_SMTP al usuario que lo requiera

Si el paquete UTL_SMTP no tiene acceso de ejecución publico, utilizando la cuenta SYS, se debe asignar el privilegio EXECUTE sobre dicho paquete a los usuarios que lo van a utilizar.

GRANT EXECUTE ON SYS.UTL_SMTP TO Usuarioxxx;

Donde Usuarioxxx, corresponde al usuario que va a utilizar el paquete

4: Crear las listas de control de acceso (ACL) para que Oracle permita acceso al servidor de correo SMTP.

 Para mayor ampliación de para que sirven y como se administran las ACLs, pueden accesar el enlace de Haciendo TI, Oracle ACL Lista de Control de Acceso.

4.1 Verificando las ACLs que se están utilizando.

Se pueden consultar las ACLs que está administrando la base de datos, y verificar que no exista creada una acl para accesar el servidor SMTP. La siguiente instrucción SQL permite la consulta en referencia.

SELECT HOST, LOWER_POR, UPPER_PORT, ACL FROM DBA_NETWORK_ACLS;

HOST                   LOWER_PORT    UPPER_PORT,  ACCL

------------------------------------------------------------------------

192.168.030.999             25               25            mail_server_access.xml      

4.2 Servicio SMTP con acceso publico

4.2a Creacion de Listas de Control de Acceso para permitir acceso publico al servidor SMTP.

Para crear una ACL  que servirá para acceder a un servidor SMTP y su puerto de enlace, se ejecuta el procedimiento CREATE_ACL del paquete DBMS_NETWORK_ACL_ADMIN, el cual tiene como objetivo registros de control de accesos de los usuarios hacia el servidor SMTP. A continuación se muestra un ejemplo de creación de una ACL para accesar un servidor SMTP.

     BEGIN   

              DBMS_NETWORK_ACL_ADMIN.CREATE_ACL

                                                                          (acl          => 'mail_server_access.xml',

                                                                           description  => 'Permiso para accesar  e-mail server mail.access.com.', 

                                                                           principal    => 'PUBLIC',   

                                                                           is_grant     => TRUE,

                                                                           privilege    => 'connect'); 

             COMMIT;

     END;

4.2b En caso se utilice el Id del servidor SMTP en lugar de la IP, se debe asignar el privilegio 'resolve' al usuario 'PUBLIC' para que pueda resolver la dirección IP a partir del ID del servidor SMTP.

 

Para dar un privilegio (connect, resolve)  de acceso por medio de una ACL a un usuario, se ejecuta el procedimiento ADD_PRIVILEGE del paquete DBMS_NETWORK_ACL_ADMIN, el cual tiene como objetivo crear registros con el tipo de privilegio asignado para accesar un servidor SMTP. El privilegio 'resolve' permite resolver la dirección IP del servidor SMTP por medio del nombre del servidor SMTP. A continuación se muestra un ejemplo de adición de privilegio para accesar un servidor SMTP. 

   BEGIN

              DBMS_NETWORK_ACL_ADMIN.add_privilege 

                                                                         ( acl          => 'mail_server_access.xml',

                                                                           principal    => 'PUBLIC', 

                                                                           is_grant     => TRUE,

                                                                           privilege    => 'resolve'); 

             COMMIT; 

     END;

4.3 Servicio SMTP con acceso privado

4.3a Creacion de Listas de Control de Acceso para permitir acceso privado al servido SMTP a un usuario.

Para crear una ACL  que servirá para acceder a un servidor SMTP y su puerto de enlace, se ejecuta el procedimiento CREATE_ACL del paquete DBMS_NETWORK_ACL_ADMIN, el cual tiene como objetivo registros de control de accesos de los usuarios hacia el servidor SMTP. A continuación se muestra un ejemplo de creación de una ACL para accesar un servidor SMTP.

 

     BEGIN   

              DBMS_NETWORK_ACL_ADMIN.CREATE_ACL

                                                                          (acl          => 'mail_server_access.xml',

                                                                           description  => 'Permiso para accesar  e-mail server mail.access.com.', 

                                                                           principal    => 'Usuarioxxx',   

                                                                           is_grant     => TRUE,

                                                                           privilege    => 'connect'); 

             COMMIT;

     END;

4.3b En caso se utilice el Id del servidor SMTP en lugar de la IP, se debe asignar el privilegio 'resolve' al usuario 'Usuarioxxx' para que pueda resolver la dirección IP a partir del ID del servidor SMTP.

 

Para dar un privilegio (connect, resolve)  de acceso por medio de una ACL a un usuario, se ejecuta el procedimiento ADD_PRIVILEGE del paquete DBMS_NETWORK_ACL_ADMIN, el cual tiene como objetivo crear registros con el tipo de privilegio asignado para accesar un servidor SMTP. El privilegio 'resolve' permite resolver la dirección IP del servidor SMTP por medio del nombre del servidor SMTP. A continuación se muestra un ejemplo de adición de privilegio para accesar un servidor SMTP. 

   BEGIN

              DBMS_NETWORK_ACL_ADMIN.add_privilege 

                                                                         ( acl          => 'mail_server_access.xml',

                                                                           principal    => 'Usuarioxxx', 

                                                                           is_grant     => TRUE,

                                                                           privilege    => 'resolve'); 

             COMMIT; 

     END;

 

5: Asociar los nombres o direcciones IP de los servidores SMTP que se desea acceder.

Para asociar una ACL a un servidor SMTP y puerto de enlace, se ejecuta el procedimiento ASSIGN_ACL del paquete DBMS_NETWORK_ACL_ADMIN, el cual tiene como objetivo llevar a cabo dicha asociación. A continuación se muestra un ejemplo de asociación de un servidor SMTP con una ACL creada para tal propósito (Referirse a numeral 4).

BEGIN   

        DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL

                                                                 (  acl          => 'mail_server_access.xml',

                                                                    host         => '192.168.030.999',  -- Dirección IP o Nombre de servidor SMTP

                                                                    lower_port   => 25,                    -- Normalmente los puertos que se utilizan para el servidor SMTP son el 25 o 465

                                                                    upper_port   => 25    );

        COMMIT;

END;

Para garantizar que la habilitación de ACLs para acceder al servidor SMTP,  está correcta, verificar ejecutando las instrucciones PLSQL descritas en el numeral 2.1.2.

6: Elaborar el componente PL/SQL (Paquete o procedimiento) que se utilizará para el envio de email, ya sea con o sin adjuntos.

Les comparto el enlace de Roger DBA - Envío de Correos utilizando UTL_SMTP, el cual muestra un excelente ejemplo de elaboración de componentes para envio de correos, con o sin adjuntos, así como, el envio de multiples recipientes, Cc y Bcc.

Normalmente el Flujo de ejecución de tareas para crear y enviar un email via SMTP se ilustra a continuación:

 1. Estableciendo conexion y autenticacion con el servidor SMTP

 2. Escribir encabezado MIME

 3. Escribir cuerpo del mensaje incluyendo adjuntos de ser necesario

 4. Escribir Firma del remitente con indicaciones de contactos para el cliente 

 5. Cerrar escritura y Enviar Email

 6. Finalizar Conexion con el servidor SMTP

 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Se anexa Script del procedimiento  Prc_Send_Mail_Attach.sql, el cual es una versión personalizada del componente de ejemplo de Roger DBA - Envío de Correos utilizando UTL_SMTP. Este procedimiento sirvio de base para crear un componente de negocios para atender servicios de envio de email via SMTP con Oracle 11g y 12C.

Para mayor información sobre la funcionalidad del paquete UTL_STMP puede consultar el manual de Oracle Packages References.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

7: Una vez instalado el envio de correo por medio del paquete UTL_SMTP, utilizar el motor PL/SQL para envio de correo cuantas veces sea necesario.

 Se recomienda que cuando se cree un componente para envio de emails, se haga utilizando las mejores práctica de arquitectura de servicios compartidos, a fin que éste pueda ser consumido desde cualquier proceso que demande el envio de correos, sea este un servicio de envio de email individual o un servicio de envio masivo de correos.

Enlaces externos:

A continuación se detallan una serie de enlaces con contenido de mucha inducción para utilizar los paquetes UTL_SMTP y UTL_MAIL para el envio de correos por medio de la base de datos Oracle:

  1. Cómo enviar email con adjuntos
  2. Envio de correo con adjuntos Blog oraculoparatodos
  3. Oracle tips 10g utl_mail
  4. Para que se utiliza el paquete utl_encode
  5. Enviando email desde oracle 11g utilizando ult_smtp
  6. Enviando email desde la base de datos
  7. Uso de Listas de Control de Acceso (ACL) para accesar servicios de ...  
  8. Listas de control de acceso Oracle ACL Haciendoti.com
  9. Oracle Packages References
  10. Publicando reporte con oracle 11g reporot services
  11. Forum envio email y smss utilizando plsql Dataprix.com
  12. Enviando correos utilizando utl_mail
  13. Enviando correos con plsql dba-oracle.com
  14. Notas / tips para envio de correos utilizando plsql dba-oracle.com
  15. Envio de correos con adjunto dba-oracle.com
  16. Envio de correo con adjunto comunidad Oracle
  17. Envio de correos con adjuntos stackoverflow.com
  18. Envio de email con Oracle ult_smtp Techreplulic.com
  19. Envio de correos con adjuntos utilizando utl_smtp Techrepublic.com

  Eduardo Escalante Rosales

  TI - Consultor, El Salvador, America Central

  Email:  eescalanteba@hotmail.com

  Twitter: @eescalanteba

Visitas: 5801

Comentario de Anibal G. Garcia Soto el agosto 28, 2015 a las 7:10pm

Que buen aporte , gracias por el detalle y la explicacion paso a paso .  Excelentes fotos.

Mi humilde aporte , como asegurar quel acl y usuario que se menciona si tiene ya privilegios para enviar correos


SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('mailserver_acces.xml.xml', 'Usuarioxxx', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('mailserver_acces.xml.xml', 'Usuarioxxx', 'resolve'),
1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
FROM dual;

La salida correcta deberia ser Granted en ambos casos.

Comentario de Eduardo Escalante Rosales el agosto 29, 2015 a las 9:09am

Gracias Anibal, extuvistes en el OTN Tours 2015, donde estuviste. Yo participe en el OTN Tour 2015, El Salvador.

Comentario de Anibal G. Garcia Soto el septiembre 2, 2015 a las 3:51pm

Eduardo un placer intercambiar ideas y comentarios , mi twitter AgarciaDBA , alli encontraras la confenrencia que tuve el agrado de mostrar . He podido participar en los OTN 2013,2014.2015 en Guatemala.

Comentario

¡Tienes que ser miembro de Comunidad Oracle Hispana para agregar comentarios!

Participar en Comunidad Oracle Hispana

© 2017   Creado por Fernando Garcia.   Tecnología de

Insignias  |  Informar un problema  |  Términos de servicio