Roger DBA: abril 2012

Nueva Versión!

Se ha publicado una actualización al procedimiento en el siguiente enlace

Puedes seguir leyendo este post como referencia.

Desde que inicié en el mundo Oracle, me he encontrado con muchas implementaciones que te permiten hacer ciertos procesos desde la BD sin necesidad de hacerlos desde un programa externo. En esta ocasión veremos el envío de correos.

Tanto en la documentación de Oracle como en la web encontrarás muchos ejemplos y documentación, sin embargo ninguno (creo yo aplicado a un entorno real) y sin "partes ocultas".

A continuación mostraré un procedimiento que utiliza el package UTL_SMTP con el cual se pueden enviar correos a múltiples destinatarios en el Para, CC y CCO, así como múltiples adjuntos.

He de mencionar que tiene sus requerimientos.

La lista de destinatarios y de archivos debe estar separadas por punto y coma (;) y sin espacios entre ellos (esta condición la pueden cambiar modificando el fuente), Ejemplo: 'usuario.uno@dominio.com;usuario.dos@dominio.dos.mx;usuario.tres@otrodominio.com'
El procedimiento hace uso de un directorio lógico en donde se alojan los archivos a adjuntar, por lo que es necesario creen el directorio con el nombre que requieran en su BD y ahi coloquen los archivos.
Se debe especificar el tipo de correo, ya sea HTML (en el parametro se coloca 'HTML') o Texto, que es el valor por default.

Ya con esto aclarado y recordandoles que hay comentarios dentro del código, puedo decirles que con este ejemplo pueden basarse para hacer uno sin envío de adjuntos (innecesario para mi gusto, pues con el solo hecho de mandar la cadena vacía, no se manda nada).

create or replace procedure P_SendMailAttach

(

p_DestinatariosPara VARCHAR2,

p_DestinatariosCC VARCHAR2 DEFAULT NULL,

p_DestinatariosBcc VARCHAR2 DEFAULT NULL,

p_Mensaje VARCHAR2 DEFAULT 'Haga caso omiso a este mensaje',

p_Remitente VARCHAR2 DEFAULT 'usuario@midominio',

p_Asunto VARCHAR2 DEFAULT 'Este correo no tiene asunto',

p_TipoMensaje VARCHAR2 DEFAULT 'TEXT',

p_Directorio VARCHAR2 DEFAULT 'ATTACHMENTS', -- Directorio lógico por default

p_ArchivosAdjuntos VARCHAR2

)

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

-- Nombre : P_SendMailAttach

-- Autor : Ing. Rogelio "Wazu" Rodriguez

-- Descripcion : Envio de correos utilizando el package UTL_SMTP

-- Requerimientos : UTL_SMTP

-- Restricciones :

-- Revisiones:

-- Fecha Desarrollador Cambio

-- =========== ======================== =================================================

-- 03-APR-2012 Rogelio Wazu Rodriguez Creación Inicial
-- 30-JUL-2012 Rogelio Wazu Rodriguez Se modifica en el procedure
file_attach en content transfer
sea después de Disposition

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

c utl_smtp.connection;

--Procedimiento para escritura de Headers

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

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS

BEGIN

utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);

END;

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

--Procedimiento para Adición de Destinatarios

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

PROCEDURE add_rcpt(p_Destinatarios in VARCHAR2) AS

l_Cadena varchar2(500) := p_Destinatarios;

l_LargoCadena number;

l_Comas number;

l_PosicionComa number := 0;

l_Destinatario varchar2(100);

BEGIN

l_LargoCadena := length(l_Cadena);

l_Comas := l_LargoCadena-length(replace(l_Cadena,';'));

--Bloque 1 Asginación del RCPT

IF l_Comas > 0 THEN

FOR l_segmento IN 1 .. l_Comas LOOP

l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, instr(l_Cadena,';',1,l_segmento) - (l_PosicionComa + 1));

l_PosicionComa := instr(l_Cadena,';',1,l_Segmento);

utl_smtp.rcpt(c, l_Destinatario);

END LOOP;

END IF;

-- Fin de Bloque 1

-- Bloque 2: Para inserción del ultimo recipient solicitado (o el primero, si es unico)

l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, l_LargoCadena);

utl_smtp.rcpt(c, l_Destinatario);

-- Fin Bloque 2

END;

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

-- Procedimiento de adición de cabeceras para destinatarios

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

PROCEDURE add_headers_rcpt(p_Destinatarios IN VARCHAR2, p_Type IN VARCHAR2) AS

l_Cadena varchar2(500) := p_Destinatarios;

l_LargoCadena number;

l_Comas number;

l_PosicionComa number := 0;

l_Destinatario varchar2(100);

BEGIN

l_LargoCadena := length(l_Cadena);

l_Comas := l_LargoCadena-length(replace(l_Cadena,';'));

--Bloque 1 Asginación del Destinatario al header

IF l_Comas > 0 THEN

FOR l_segmento IN 1 .. l_Comas LOOP

l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, instr(l_Cadena,';',1,l_segmento) - (l_PosicionComa + 1));

l_PosicionComa := instr(l_Cadena,';',1,l_Segmento);

-- Se generan los encabezados para envio, en caso de Bcc no se agrega el Header

IF p_Type = 'TO' THEN

send_header('To', l_Destinatario);

ELSE

send_header('Cc', l_Destinatario);

END IF;

END LOOP;

END IF;

-- Fin de Bloque 1

-- Bloque 2: Para inserción del ultimo recipient solicitado (o el primero, si es unico)

l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, l_LargoCadena);

IF p_Type = 'TO' THEN

send_header('To', l_Destinatario);

ELSE

send_header('Cc', l_Destinatario);

END IF;

-- Fin Bloque 2

END;

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

-- Procedimiento para adjuntar los archivos al stream del correo

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

PROCEDURE file_attach(p_Archivo varchar2) AS

-- Variables para el procesamiento de Archivos

rfile RAW(57);

flen NUMBER;

bsize NUMBER;

src_file bfile;

buffer_ integer := 57;

i integer := 1;

BEGIN

-- Escribir cabecera MIME

utl_smtp.write_data(c,'--MIME.Bound'||utl_tcp.CRLF);

send_header('Content-Type','application/octet-stream; name="' || p_Archivo || '"');

send_header('Content-Disposition', 'attachment; filename="' || p_Archivo || '"');
send_header('Content-Transfer-Encoding', 'base64' );

utl_smtp.write_data(c, utl_tcp.CRLF);

-- Adición del Archivo

src_file := bfilename(p_Directorio, p_Archivo);

flen := dbms_lob.getlength(src_file);

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

while i < flen loop

dbms_lob.read( src_file, buffer_, i, rfile );

utl_smtp.write_raw_data(c, utl_encode.base64_encode(rfile));

utl_smtp.write_data(c, utl_tcp.CRLF);

i := i + buffer_;

end loop while_loop;

dbms_lob.fileclose(src_file);

utl_smtp.write_data(c, utl_tcp.CRLF||utl_tcp.CRLF);

END;

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

--Procedimiento para separación y Adición de Archivos Adjuntos

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

PROCEDURE add_attachments(p_Adjuntos in VARCHAR2) AS

l_Cadena varchar2(500) := p_Adjuntos;

l_LargoCadena number;

l_Comas number;

l_PosicionComa number := 0;

l_Archivo varchar2(100);

-- Variables para el procesamiento de Archivos

rfile RAW(57);

flen NUMBER;

bsize NUMBER;

src_file bfile;

buffer_ integer := 57;

i integer := 1;

BEGIN

l_LargoCadena := length(l_Cadena);

l_Comas := l_LargoCadena-length(replace(l_Cadena,';'));

--Bloque 1 Adición del Archivo

IF l_Comas > 0 THEN

FOR l_segmento IN 1 .. l_Comas LOOP

l_Archivo := substr(l_Cadena, l_PosicionComa + 1, instr(l_Cadena,';',1,l_segmento) - (l_PosicionComa + 1));

file_attach(l_Archivo);

l_PosicionComa := instr(l_Cadena,';',1,l_Segmento);

END LOOP;

END IF;

-- Fin de Bloque 1

-- Bloque 2: Para inserción del ultimo archivo (o el primero, si es unico)

l_Archivo := substr(l_Cadena, l_PosicionComa + 1, l_LargoCadena);

file_attach(l_Archivo);

-- Fin Bloque 2

END;

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

-- Inicio del Programa Core para el correo

BEGIN

-- Bloque de Apertura de Conexión

c := utl_smtp.open_connection('165.254.254.32',25); --Recuerden que puede ser el nombre NetBios o la IP

utl_smtp.helo(c, '165.254.254.32');

utl_smtp.mail(c, p_Remitente);-- Remitente

-- Bloque de Adición de Destinatarios

IF p_DestinatariosPara IS NOT NULL THEN

add_rcpt(p_DestinatariosPara);

END IF;

IF p_DestinatariosCC IS NOT NULL THEN

add_rcpt(p_DestinatariosCC);

END IF;

IF p_DestinatariosBcc IS NOT NULL THEN

add_rcpt(p_DestinatariosBcc);

END IF;

--Bloque de Apertura de Datos

utl_smtp.open_data(c);

-- Bloque de Adición de Cabeceras de Mail

IF p_DestinatariosPara IS NOT NULL THEN

add_headers_rcpt(p_DestinatariosPara, 'TO');

END IF;

IF p_DestinatariosCC IS NOT NULL THEN

add_headers_rcpt(p_DestinatariosCC, 'CC');

END IF;

send_header('Subject', p_Asunto); --Asunto

send_header('From', p_Remitente); -- De

send_header('MIME-Version','1.0');

send_header('Content-Type','multipart/mixed; boundary="MIME.Bound"');

utl_smtp.write_data(c,'--MIME.Bound'||utl_tcp.CRLF);

send_header('MIME-Version','1.0');

IF p_Mensaje IS NOT NULL THEN

IF p_TipoMensaje = 'TEXT' THEN

send_header('MIME-Version','1.0');

send_header('Content-Type','text/plain; charset=us-ascii');

send_header('Content-Disposition', 'inline');

ELSE

send_header('MIME-Version','1.0');

send_header('Content-Type', 'text/html;charset=windows-1252');

send_header('Content-Disposition', 'inline');

send_header('Content-Transfer_Encoding', '8bit');

END IF;

-- Bloque de Escritura del Mensaje

utl_smtp.write_data(c, utl_tcp.CRLF ||p_Mensaje||utl_tcp.CRLF);

utl_smtp.write_data(c, utl_tcp.CRLF);

-- Bloque de Envio de Adjuntos

add_attachments(p_ArchivosAdjuntos);

utl_smtp.write_data(c, utl_tcp.CRLF||'--MIME.Bound--'||utl_tcp.CRLF);

-- Bloque de Cierre de Datos y Envio del Mail

utl_smtp.close_data(c);

-- Bloque de Cierre de Conexión

utl_smtp.quit(c);

EXCEPTION

WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then

-- Bloque de Cierre de Conexión

utl_smtp.quit(c);

raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

WHEN OTHERS THEN

-- Bloque de Cierre de Conexión

utl_smtp.quit(c);

raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

END;

-- Ejemplo de uso

exec P_SENDMAILATTACH(p_DestinatariosPara =>'usuario.uno@empresa1.com;usuario.dos@empresa2.com',

p_DestinatariosCC => 'usuario@otraempresa.com',

p_Remitente => 'InformesEmpresa1@empresa1.com',

p_Mensaje =>'<h1> Informe </h1> <p>Cuerpo del Informe</p>',

p_Asunto => 'Envío de Informe Empresa1',

p_ArchivosAdjuntos => 'a6.pdf;a1.csv;A3.xml;A5.PDF;a6.txt',

p_TipoMensaje => 'HTML');

Espero les sea de utilidad el código y les confirmo que funciona al 100%, cualquier pregunta o comentario, pueden dejarlo en este post.

Hace tiempo, en mi anterior trabajo necesitábamos una forma de ejecutar tareas programadas que no dependiesen completamente de los Jobs de Oracle, primero por que teníamos que solicitarle al DBA los creara, no podíamos monitorearlo y los logs en caso de errores los teníamos que llevar por nuestra cuenta y el tiempo programado de ejecución no era preciso.

Al final se creo una aplicación que guardaba todas las tareas por ejecutar, con las fechas de inicio y su programación y se mandaba a llamar desde un Job Oracle un procedimiento que leía todos los procedimientos programados para esa hora y los iba ejecutando uno a uno, generaba la siguiente fecha de ejecución y en caso de errores guardaba el log en una tabla, para con ello el trabajo no se desactivara en caso de error.

Cuando llego a mi nueva empresa, tengo la misma problemática con los Jobs en la versión 9i, había momentos en que los Jobs se empalmaban por mas que les asignara una fecha inicial diferente (los tiempos de ejecución intervenían en el cálculo de las nuevas fechas) y no tenía modo de monitorear si se había ejecutado correctamente o no, por lo que el error era detectado posteriormente por el desarrollador que recibía el reclamo de que no había llegado o no se había generado el reporte.

Ya con la migración de la Base de Datos, me puse a Investigar un poco sobre una característica existente desde la versión 10g, los Scheduled Jobs.

Los Scheduled Jobs, no es más que la programación de ejecución de algún programa PL/SQL, o comando externo desde la Base de Datos a una fecha y hora determinada.

¿Cual es la diferencia con los Jobs normales? Primero, que no le afecta el tiempo de ejecución del job, segundo, guardan un log de ejecuciones (programable para solo cuando sucedan errores, sea satisfactorio o ambos) y tercero, no le afecta la configuración de Database Vault al querer modificarlos/ejecutarlos con el usuario SYS si el dueño es otro usuario.

Lo que he observado, es que finalmente, un Scheduled Job internamente cuando llega la hora de ejecutarse, crea un Job, se ejecuta y lo elimina, por lo que se tiene que tener especial cuidado en la programación de tiempo, para evitar empalmes de ejecución del mismo procedimiento (posibles bloqueos masivos o sesiones colgadas con procesos a medio terminar).

Para implementar un Scheduled Job se ejecuta un script como este ejemplo:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'
,start_date => TO_TIMESTAMP_TZ('2012/03/08 13:35:00.000000 -06:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'
,job_type => 'STORED_PROCEDURE'
,job_action => 'SERVICIOS_BD.P_DesasignaPrivilegios'
,end_date => NULL
,comments => 'Desasignación de Privilegios asignados a Desarrolladores por el procedimiento de Autoasignación de Privilegios de Base de Datos'
);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_FULL);

DBMS_SCHEDULER.ENABLE(name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB');

END;
/

La primer parte crea el Scheduled Job y la segunda lo activa, ya que por default estos no son activados al crearse.

Una observación es con la especificación de la fecha inicio de ejecución y el horario de verano, en el caso de mi país (México) y por mi uso horario (Ciudad de México) el uso horario que me corresponde es el GMT -6:00, sin embargo, cuando entra en rigor el horario de verano pasa a ser GMT -5:00 por lo que el Scheduled Job comienza a ejecutarse con una hora de retraso por su programación inicial.

En los países que utilicen el horario de Verano, cuando se especifique la fecha de inicio deberán utilizar el formato con el código de zona de tiempo (TIME ZONE) que le corresponda.

En mi caso, quedaría de la siguiente manera:

BEGIN

DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'

,start_date => TO_TIMESTAMP_TZ('2012/03/08 13:35:00 America/Mexico_City','yyyy/mm/dd hh24:mi:ss tzr')

,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'

,job_type => 'STORED_PROCEDURE'

,job_action => 'SERVICIOS_BD.P_DesasignaPrivilegios'

,end_date => NULL

,comments => 'Desasignación de Privilegios asignados a Desarrolladores por el procedimiento de Autoasignación de Privilegios de Base de Datos'

);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'

,attribute => 'LOGGING_LEVEL'

,value => SYS.DBMS_SCHEDULER.LOGGING_FULL);

DBMS_SCHEDULER.ENABLE(name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB');

END;

Noten los cambios en la especificación de la hora, en la cual se agregó mi TIME ZONE y la máscara de formato, que se le agrega el TZR, con ello Oracle toma en cuenta que esa zona de Tiempo le es aplicado el horario de verano, y así considera en los cálculos de la próxima fecha de ejecución el cambio de horario.

Si necesitas consultar los códigos de Time Zone, puedes hacer un Select a la vista V$TIMEZONE_NAMES.

Lo del horario de verano lo encontré en esta página http://deryaoktay.wordpress.com/2010/01/21/oracle-job-scheduler-runs-my-jobs-one-hour-earlier/

Los scheduled jobs permiten muchas más configuraciones, pero para las necesidades que tengo ahora, con esto fué suficiente.

Me queda como tarea el habilitar las notificaciones automáticas vía correo electrónico de las ejecuciones de estas tareas.

Roger DBA

miércoles, 18 de abril de 2012

Envío de Correos utilizando UTL_SMTP

lunes, 9 de abril de 2012

Tareas Programadas con Oracle Schedule Jobs