Oracle Live

15/10/2016

Database – Scheduler File Watcher

Filed under: Database — mogukiller @ 9:27 am

Comandos básicos para trabajar con file watcher en el scheduler de oracle

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE', 'REPEAT_INTERVAL',    'FREQ=MINUTELY;INTERVAL=2');
END;
/
alter user test account unlock;

grant dba to test

begin
    dbms_scheduler.set_attribute ('FILE_WATCHER_SCHEDULE', 'REPEAT_INTERVAL', 'FREQ=MINUTELY;INTERVAL=4');
end;
/

-- Creamos las credencias

begin
  dbms_scheduler.create_credential(
     credential_name => 'test_credential',
     username        => 'oracle',
     password        => 'nuevo13');
end;
/


drop table  eod_reports;
create table eod_reports (when timestamp, file_name varchar2(100), file_size number, processed char(1), job_name varchar2(4000));
 
create or replace procedure q_eod_report   (payload IN sys.scheduler_filewatcher_result, job_name IN VARCHAR2(4000)) 
IS 
  vFile utl_file.file_type;
BEGIN  
  vFile := utl_file.fopen('DATA_PUMP_DIR', 'FileWatcher.txt', 'w', NULL);
  utl_file.put_line(vFile, 'File has arrived '||job_name, TRUE);
  utl_file.fclose(vFile);   
end;
/

  insert into eod_reports values 
     (payload.file_timestamp,
      payload.directory_path || '/' || payload.actual_file_name,
      payload.file_size,
      'N',job_name);


-- Creamos el programa pasando dos parametros
 
begin
  dbms_scheduler.create_program(
    program_name        => 'eod_prog',
    program_type        => 'stored_procedure',
    program_action      => 'q_eod_report',
    number_of_arguments => 2,
    enabled             => false);
	
  dbms_scheduler.define_metadata_argument(
    program_name        => 'eod_prog',
    metadata_attribute  => 'event_message',
    argument_position   => 1);
  
  dbms_scheduler.define_metadata_argument(
    program_name        => 'eod_prog',
    metadata_attribute  => 'job_name',
    argument_position   => 2);	
  dbms_scheduler.enable('eod_prog');
end;
/

-- Creamos el file_watcher
 
begin
  dbms_scheduler.create_file_watcher(
    file_watcher_name => 'eod_reports_watcher',
    directory_path    => '/export/home/lmds/pruebas',
    file_name         => 'pruebas_*',
    credential_name   => 'test_credential',
    destination       => null,
    enabled           => false);
end;
/

-- Creamos el job
 
begin
  dbms_scheduler.create_job(
    job_name        => 'eod_job',
    program_name    => 'eod_prog',
    event_condition => null,
    queue_spec      => 'eod_reports_watcher',
    auto_drop       => false,
    enabled         => false);
  dbms_scheduler.set_attribute('eod_job','parallel_instances',true);
end;
/
 
exec dbms_scheduler.enable('eod_reports_watcher,eod_job');

SELECT * FROM eod_reports;

-- Disable jobs y file watcher
exec dbms_scheduler.disable('eod_job');
exec dbms_scheduler.disable('eod_reports_watcher,eod_job');


BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE('EOD_REPORTS_WATCHER', 'FILE_NAME',   'NAC_*');
END;
/

SELECT * FROM DBA_SCHEDULER_FILE_WATCHERS where FILE_WATCHER_NAME='EOD_REPORTS_WATCHER';

-- Para obtener el valor de un atributo
DECLARE
	SALIDA BOOLEAN;
BEGIN
	DBMS_SCHEDULER.GET_ATTRIBUTE ('EOD_JOB2','PARALLEL_INSTANCES', SALIDA);
	IF SALIDA THEN
		DBMS_OUTPUT.put_line('True');
	ELSE
		DBMS_OUTPUT.put_line('False');
	END IF;
END;
/

-- Borramos un job
exec DBMS_SCHEDULER.DROP_JOB('eod_job');
exec DBMS_SCHEDULER.DROP_FILE_WATCHER('eod_reports_watcher');
exec DBMS_SCHEDULER.DROP_PROGRAM('eod_prog');


-- Miramos la configuracion del job
COL PROGRAM_NAME FOR A30
COL JOB_ACTION FOR A30
COL SCHEDULE_NAME FOR A30
SELECT OWNER, JOB_NAME, PROGRAM_NAME, JOB_ACTION, NUMBER_OF_ARGUMENTS, SCHEDULE_NAME, FILE_WATCHER_NAME, RUN_COUNT, LAST_START_DATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME LIKE '%&job_name%' ORDER BY 2;

-- La ultima vez que ha saltado el file watcher

SELECT LAST_START_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME ='FILE_WATCHER';

SELECT * FROM TEST.EOD_REPORTS;

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: