Knowledge Base : File watcher in Oracle DB

60 Juni 2, 2022

Using the file watcher feature available since 11gR2

0. Prerequisites

  • File Watcher job in SYS must be enabled (should by by default)
  • EXECUTE on SYS.SCHEDULER_FILEWATCHER_RESULT my be granted to the user deploying the file watcher

1. Create credential

BEGIN
DBMS_SCHEDULER.CREATE_CREDENTIAL (
credential_name => 'OS_USER_CREDENTIAL',
username => 'oracle',
password => 'secret');
END;

2. Create file watcher

BEGIN
DBMS_SCHEDULER.CREATE_FILE_WATCHER (
file_watcher_name => 'TEST_FILEWATCHER',
directory_path => '/path/to/directory',
file_name => '*.txt',
credential_name => 'OS_USER_CREDENTIAL',
destination => NULL,
min_file_size => 0,
steady_state_duration => INTERVAL '1' SECOND, – important, how long does the file watcher wait for file to grow?
comments => NULL,
enabled => true);
END;

3. Create stored procedure processing the result, if needed (a job can directly be started by the file watcher if this is not necessary)

CREATE OR REPLACE
PROCEDURE proc_test_filewatcher (payload IN sys.scheduler_filewatcher_result)
IS
BEGIN
insert into filewatcher_files (upload_timestamp, file_name, file_size)
values (payload.file_timestamp, payload.directory_path||'/'||payload.actual_file_name,payload.file_size);
commit; 
END;

4. Create program to pipe metadata into procedure (as above, not necessary if you simply want to start a job)

BEGIN
sys.dbms_scheduler.create_program(
program_name => 'FILEWATCHER_PROGRAM',
program_action => 'PROC_TEST_FILEWATCHER',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => NULL,
enabled => FALSE);

sys.dbms_scheduler.define_metadata_argument (
program_name => ‚FILEWATCHER‘,
metadata_attribute => ‚event_message‘,
argument_position => 1);

sys.dbms_scheduler.enable('filewatcher');
END;

5. Create job to start program (can directly start procedure/external job/…). Also, an event condition can be used to only start the job if the file is bigger than, newer than, …

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => 'FILEWATCHER_JOB',
program_name => 'FILEWATCHER_PROGRAM',
start_date => NULL,
event_condition => NULL,
queue_spec => 'TEST_FILEWATCHER', – this specifices the file watcher created above!
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
enabled => TRUE,
auto_drop => FALSE,
comments => '',
job_style => 'REGULAR',
credential_name => NULL,
destination_name => NULL);
END;