Hello guys,
I need a help with Oracle Scheduler again.
What I need to do is to run a job when a file arrives to some specific folder.
I have read the tutorial here:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm
SELECT * FROM V$VERSION;
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
After solving this for several hours I just copied the example which is at the end of the tutorial and ran it.
begin
dbms_scheduler.create_credential(
credential_name => 'watch_credential',
username => 'xxxxx',
password => 'xxxxx');
end;
/
create table eod_reports (when timestamp, file_name varchar2(100),
file_size number, processed char(1));
create or replace procedure q_eod_report
(payload IN sys.scheduler_filewatcher_result) as
begin
insert into eod_reports values
(payload.file_timestamp,
payload.directory_path || '/' || payload.actual_file_name,
payload.file_size,
'N');
end;
/
begin
dbms_scheduler.create_program(
program_name => 'eod_prog',
program_type => 'stored_procedure',
program_action => 'q_eod_report',
number_of_arguments => 1,
enabled => false);
dbms_scheduler.define_metadata_argument(
program_name => 'eod_prog',
metadata_attribute => 'event_message',
argument_position => 1);
dbms_scheduler.enable('eod_prog');
end;
/
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'eod_reports_watcher',
directory_path => '/home/xxxxx/xxxxx/xxxx/xxxx/xxxxx/xxxx/xxxxx',
file_name => 'eod*.txt',
credential_name => 'watch_credential',
destination => null,
enabled => false);
end;
/
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');
I did three changes to the code in comparison with the example
1) Changed username and password in the credentials (username and password which I use for login to the OS)
2) Changed directory path for the file watcher (I started the directory path with the /home/...)
3) Set the event_condition parameter to Null
Finally I ran this as SYS to make the watcher check the folder every second:
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('FILE_WATCHER_SCHEDULE', 'REPEAT_INTERVAL',
'FREQ=SECONDLY');
END;
/
Still nothing happens when move some file (e.g. eod1.txt) into specified folder.
SELECT * FROM USER_SCHEDULER_JOBS;
JOB_NAME = EOD_JOB
ENABLED = TRUE
STATE = SCHEDULED
RUN_COUNT = 0
Anybody can help me with this?