msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Did You Know About File Watchers?

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 11.2.0.2 VirtualBox VM. I also ran it umodified (except for the directoy path and OS username) on an Oracle Enterprise Linux R5U6 + Oracle DB 11.2.0.2 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.

Create the program object that maps to the procedure we just created. Also create its event_message metadata argument:

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?

Update: A File Watcher uses java in the database, as a result this Scheduler feature is not available in Oracle Database XE 11g (source).


Filed in Oracle on 29 Mar 11 | Tags: ,


Reader's Comments

  1. |

    Hi.

    I think they are a great addition to the scheduler. So much batch processing is dependent on flat files being pushed about. Using a file watcher is so much nicer than having to manually pole for the presence of the file from a job, or even worse, being forced to use a shell script for do it, then initiate the job.

    I wrote about them here:

    http://www.oracle-base.com/articles/11g/SchedulerEnhancements_11gR2.php#file_watcher

    Cheers

    Tim…

  2. |

    Hi Eddie

    I have a use case for this sort of functionality (though ftp access to the db might also fit). I have often considered how to effectively implement custom monitoring of db and o/s in disconnected environments – think EM – processing of flat files in a specific format on a schedule is one method to achieve the above.

    • |

      Excellent use case. Collect data from remote systems, save it in flat files, ftp the files to a central location for reporting/monitoring purposes.

  3. |

    hmm, not sure I like the math captcha being in words but the required answer being numeric.

    • |

      You’re right, it’s not intuitive. That was meant to confuse the spammers. I fixed it; comments are moderated anyway. Thanks for the feedback.

  4. |

    Ah, lovely. I do this manually at the moment, waiting for the arrival of files for a business intelligence system — it’s quite standard functionality for ETL tools.

  5. |

    This is a great feature, with lots of potential.

    For example, I’ve worked on an inventory-management system in which the field engineers would drive out to inspect equipment many miles from the base of operations, record their findings in flat files on hand-held computers, and at end of day, return to base and upload the flat files. Once on the server, the flat files were loaded into Oracle tables.

    The file watcher would have been perfect for that application.

    There are lots of possibilities in network-management applications, too.

    Regards, David