El paquete PL/SQL DBMS_SCHEDULER para programación de trabajos

DBMS_SCHEDULER es el paquete PLSQL que reemplazó en la versión de la base de datos Oracle 10g al paquete DBMS_JOB. Aunque el paquete DBMS_JOB sigue existiendo por razones de compatibilidad, no debe utilizarse ya que es muy probable que deje de existir en futuras versiones de la base de datos Oracle. El paquete DBMS_SCHEDULER permite programar la ejecución, en los instantes que deseemos, de bloques PLSQL, así como de procedimientos y funciones PL/SQL. Por otro lado, también permite programar la ejecución de binarios y shell-scripts.

Permisos necesarios

Con permisos de DBA se tiene acceso a todas las funciones del paquete DBMS_SCHEDULER. Para administrar la programación de procesos se necesita tener la rol (role) de SCHEDULER_ADMIN. Y finalmente, para crear y ejecutar procesos bajo tu propia identidad, se necesita tener el privilegio CREATE JOB. Por otro lado, aprovecho para mencionar que el paquete DBMS_JOB requería la inicialización de un parámetro del sistema, tras lo cual se lanzaba un proceso en background encargado de coordinar la ejecución de los distintos procesos programados, pero esto ya no es necesario si utilizamos Oracle 10g y el paquete DBMS_SCHEDULER.

Programación rápida de ejecución de un bloque PL/SQL

Para programar la ejecución de un bloque PLSQL podemos utilizar directamente el siguiente código. Sin duda es la forma más rápida y sencilla de programar la ejecución de un trabajo.

BEGIN
DBMS_SCHEDULER.create_job(
job_name        => 'DEMO_BLOQUE_PROGRAMADO'
,job_type        => 'PLSQL_BLOCK'
,job_action      => 
'begin 
nom_paq.nom_proced(''valor_param'');
end;'
,start_date      => '06/11/2009 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled         => TRUE
,comments        => 'Bloque PLSQL programado');
COMMIT;
END;
/

En el código de arriba lo que hemos hecho es programar un bloque PL/SQL para que se ejecute diariamente a las dos de la mañana a partir del día 11 de junio de 2009.

Ejemplo completo de programación de un trabajo

Lo primero que debemos hacer el definir el programa que se va a ejecutar.

BEGIN
DBMS_SCHEDULER.create_program (
program_name        => 'DEMO_PROC_PROGRAMADO'
,program_type        => 'STORED_PROCEDURE'
,program_action      => 'nom_paq.nom_proced'
,number_of_arguments => 1
,enabled             => FALSE
,comments            => 'Proc. PLSQL programado');
DBMS_SCHEDULER.define_program_argument (
program_name      => 'DEMO_PROC_PROGRAMADO'
,argument_position => 1
,argument_name     => 'valor1'
,argument_type     => 'VARCHAR2'
,default_value     => 'valor por defecto');
DBMS_SCHEDULER.enable (
name => 'DEMO_PROC_PROGRAMADO');
COMMIT;
END;
/

El parámetro program_type puede tomar los siguiente valores: PLSQL_BLOCK, STORED_PROCEDURE o EXECUTABLE.

A continuación debemos definir cuando y con que frecuencia queremos ejecutar nuestro programa, es decir, debemos definir la schedule propiamente dicha.

BEGIN
DBMS_SCHEDULER.create_schedule(  
schedule_name   => 'DEMO_SCHEDULE'
,start_date      =>  '01/01/2009 01:00:00'
,repeat_interval => 'FREQ=WEEKLY'
,comments        => 'Semanalmente a las 01:00');
COMMIT;
END;
/

Si queremos deshabilitar la schedule anteriormente definida utilizaremos el siguiente código:

BEGIN
DBMS_SCHEDULER.drop_schedule(
schedule_name => 'DEMO_SCHEDULE'
,force         => TRUE );
COMMIT;
END;
/

Para establecer la frecuencia (repeat_interval) podemos utilizar las siguientes expresiones: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY y SECONDELY.

A continuación debemos definir el proceso o trabajo que queremos programar. Esto se realiza mediante la asignación de un programa a una o más schedules.

BEGIN
DBMS_SCHEDULER.create_job(
job_name      => 'DEMO_TRABAJO_PLSQL'
,program_name  =>'DEMO_PROC_PROGRAMADO'
,schedule_name =>'DEMO_SCHEDULE'
,enabled       => FALSE
,comments      => 'Semanal a las 01:00');
DBMS_SCHEDULER.set_job_argument_value(
job_name          => 'DEMO_TRABAJO_PLSQL'
,argument_position => 1
,argument_value    => 'valorparametro1');
DBMS_SCHEDULER.enable('DEMO_TRABAJO_PLSQL');
COMMIT;
END;
/

Por otro lado y según ya he comentado, con el paquete DBMS_SCHEDULER también podemos hacer correr directamente un ejecutable o una shell script:

BEGIN
DBMS_SCHEDULER.create_job
(
job_name      => 'RUN_SHELL_SCRIPT_DEMO',
schedule_name => 'DEMO_SCHEDULE',
job_type      => 'EXECUTABLE',
job_action    => '/home/shell/demo_script.sh',
enabled       => TRUE,
comments      => 'Ejecución de un shell script'
);
COMMIT;
END;
/

Otros paquetes estándares del PLSQL: DBMS_LOCK, DBMS_SQL y DBMS_OUTPUT.