Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle scheduler again - file watcher

xxsawerOct 23 2012 — edited Oct 25 2012
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 22 2012
Added on Oct 23 2012
5 comments
1,614 views