Starting with Oracle Database 11g Release 2, an event-based Scheduler job can be started based on the arrival of a file in a directory on the server. The file arrival event is raised by a new Scheduler object called a file watcher.
As per the docs: A file watcher defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.
Ittichai has an excellent example of using the file watcher. The following is similar example, but uses BLOBs instead of CLOBs. In this example, we want a Scheduler job to be triggered as soon as an image file is deposited into a directory on the database server. We want the job to be triggered once for each file. The job reads the image file and saves it into a BLOB column in a table. We will then use Oracle SQL Developer to retrieve the image from the table and view it in the built-in viewer.
I ran the code below on a Windows XP + Oracle DB 220.127.116.11 VirtualBox VM. I also ran it umodified (except for the directoy path and OS username) on an Oracle Enterprise Linux R5U6 + Oracle DB 18.104.22.168 VM.
First, let’s create a new database user and grant it all the privileges needed to run our file watcher example:
CREATE USER john IDENTIFIED BY john; GRANT CREATE SESSION TO john; GRANT CREATE PROCEDURE TO john; GRANT CREATE TABLE TO john; GRANT CREATE JOB TO john; GRANT CREATE ANY DIRECTORY TO john; GRANT CREATE EXTERNAL JOB TO john; ALTER USER john QUOTA UNLIMITED ON users;
Create the directory object where the image files will be deposited in.
SQL> CREATE OR REPLACE DIRECTORY blob_dir AS 'C:\TEMP'; Directory created.
Create a credential object to be used by the file watcher:
SQL> BEGIN 2 DBMS_SCHEDULER.create_credential ( 3 'oracle_credential', 4 'os_user', 5 'os_password'); 6 END; 7 / PL/SQL procedure successfully completed.
Create a table to store the information about and content of the processed file:
SQL> CREATE TABLE images 2 ( 3 file_name VARCHAR2 (100), 4 file_size NUMBER, 5 file_content BLOB, 6 uploaded_on TIMESTAMP 7 ); Table created.
Create the procedure that will process the file and store its content in the table. Scheduler_filewatcher_result is the data type of a file arrival event message.
SQL> CREATE OR REPLACE PROCEDURE process_image_files ( 2 payload IN SYS.scheduler_filewatcher_result) 3 IS 4 l_blob BLOB; 5 l_bfile BFILE; 6 BEGIN 7 INSERT INTO images ( 8 file_name, 9 file_size, 10 file_content, 11 uploaded_on) 12 VALUES ( 13 payload.directory_path || '\' || payload.actual_file_name, 14 payload.file_size, 15 EMPTY_BLOB (), 16 payload.file_timestamp) 17 RETURNING file_content 18 INTO l_blob; 19 20 l_bfile := BFILENAME ('BLOB_DIR', payload.actual_file_name); 21 DBMS_LOB.open (l_bfile, DBMS_LOB.lob_readonly); 22 DBMS_LOB.open (l_blob, DBMS_LOB.lob_readwrite); 23 DBMS_LOB.loadfromfile ( 24 dest_lob => l_blob, 25 src_lob => l_bfile, 26 amount => DBMS_LOB.getlength (l_bfile)); 27 DBMS_LOB.close (l_blob); 28 DBMS_LOB.close (l_bfile); 29 END process_image_files; 30 / Procedure created.
SQL> BEGIN 2 DBMS_SCHEDULER.create_program ( 3 program_name => 'image_watcher_p', 4 program_type => 'stored_procedure', 5 program_action => 'process_image_files', 6 number_of_arguments => 1, 7 enabled => FALSE); 8 DBMS_SCHEDULER.define_metadata_argument ( 9 program_name => 'image_watcher_p', 10 metadata_attribute => 'event_message', 11 argument_position => 1); 12 DBMS_SCHEDULER.enable ('image_watcher_p'); 13 END; 14 / PL/SQL procedure successfully completed.
Now, create the file watcher object. We are only interested in .jpg files in this example:
SQL> BEGIN 2 DBMS_SCHEDULER.create_file_watcher ( 3 file_watcher_name => 'image_watcher_fw', 4 directory_path => 'C:\TEMP', 5 file_name => '*.jpg', 6 credential_name => 'oracle_credential', 7 destination => NULL, 8 enabled => FALSE); 9 END; 10 / PL/SQL procedure successfully completed.
Create an event-based job that references the file watcher (as indicated in the queue_spec). We are only interested in files that have a size greater than zero (as indicated by the event_condition). We set parallel_instances to true because we want the job to run for each instance of the file arrival event, even if the job is already processing a previous event. In other words, we want the job to have the ability to process multiple files at once:
SQL> BEGIN 2 DBMS_SCHEDULER.create_job ( 3 job_name => 'image_load_j', 4 program_name => 'image_watcher_p', 5 event_condition => 'tab.user_data.file_size > 0', 6 queue_spec => 'image_watcher_fw', 7 auto_drop => FALSE, 8 enabled => FALSE); 9 10 DBMS_SCHEDULER.set_attribute('image_load_j','parallel_instances',TRUE); 11 END; 12 / PL/SQL procedure successfully completed.
Enable the file watcher and the job:
SQL> BEGIN 2 DBMS_SCHEDULER.enable ('image_watcher_fw,image_load_j'); 3 END; 4 / PL/SQL procedure successfully completed.
Now we are all set. Let’s copy a couple of .jpg files to C:\TEMP:
After about 10 minutes, the images are picked up, processed and saved into the database. To view the images using Oracle SQL Developer, query the table images, double click on a BLOB cell in the FILE_CONTENT column, click on the edit button (the pen). In the pop-up window check the View As Image box.
File watchers check for the arrival of files every 10 minutes by default. To change this interval: Connect to the database as the SYS user and change the REPEAT_INTERVAL attribute of the predefined schedule SYS.FILE_WATCHER_SCHEDULE. For example, to change it to two minutes:
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( 'FILE_WATCHER_SCHEDULE', 'REPEAT_INTERVAL', 'FREQ=MINUTELY;INTERVAL=2'); END; /
You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.
I wonder what other possible uses file watchers could have. Can you think of any?