Programar la ejecución de un procedimiento usando DBMS_SCHEDULER

Supongamos que se tiene el siguiente procedimiento sync_proc en el esquema esquemaX, el cual realiza algunas tareas de sincronizacion en algunas fuentes, y que dicho procedimiento tendrá que ejecutarse cada hora apartir de la fecha  de su activación.
Podemos notar en este caso que nuestro procedimiento tiene como entrada un parámetro de tipo numerico para su ejecución
--
-- Procedimiento a ejecutar cada hora
--
create or replace procedure sync_proc (pVal in number)
is
begin
  insert into TABLAX select * from YY where ....
  commit;
end;
 
El paquete DBMS_SCHEDULER permite la automatizacion de tareas en momentos e intervalos de tiempo segun se requiera. Podemos apartir de este paquete definir los pasos para la creacion de dicha tarea:
 
1. Crear un programa asociado al procedimiento almacenado esquemaX.sync_proc begin
  DBMS_SCHEDULER.create_program (
    program_name        => 'prg_sync_proc',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'esquemaX.sync_proc',
    number_of_arguments => 1,
    enabled             => FALSE,
    comments            => 'Programa para sincronizar las tablas X,Y,Z');
end;
/
 
2. Creamos el argumento que será pasado al programa sync_proc
  Para este caso, el valor que pasaremos al algumento pVal será 2000
begin
  DBMS_SCHEDULER.define_program_argument(
  program_name      => 'prg_sync_proc',
  argument_position => 1,
  argument_type     => 'NUMBER',
  default_value     => 2000);
End;
/ Para eliminar el argumento se debeerá indicar la posicion del argumento a eliminar  Enc aso de querer modificar el valor de un argumento, tendrá que eliminarse y volver a recrear éste
dbms_scheduler.drop_program_argument('prg_sync_proc', 1);
 
3. Creamos el intervalo de ejecución     Crearemos entonces un intervalo de ejecucion al que llamaremos ejecucion_cada_hora que controlará la ejecucion de la tarea apartir del momento en que se active la mismaBEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'ejecucion_cada_hora',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; interval=1',
    end_date        => NULL,
    comments        => 'repeticion cada Hora');
END;
/ 
 
4. Creamos el job, asociado a la programacion y al programabegin
  DBMS_SCHEDULER.create_job (
    job_name      => 'job_sync_proc',
    program_name  => 'prg_sync_proc',
    schedule_name => 'ejecucion_cada_hora',
    enabled       => TRUE,
    comments      => 'Job para la ejecucion del programa de sync_prg cada hora');
end;
/
 
5. Habilitamos o activamos la ejecución del job:begin
DBMS_SCHEDULER.enable('prg_sync_proc');
end;
/
Por último:
Podemos consultar en la vista dba_scheduler_jobs el estado de la ejecucion de los jobs programados (ultima ejecucion, proxima ejecucion, fallas, estado entre otras caracteristicas)
SQL> SELECT owner, job_name, enabled, j.NEXT_RUN_DATE, j.FAILURE_COUNT FROM dba_scheduler_jobs j;
Cuando hubieren fallas en la ejecucion del job, podemos los detalles de éstas en la vista DBA_SCHEDULER_JOB_RUN_DETAILSejem:
SQL> SELECT ERROR#, ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'JOB_SYNC_PROC';