There are many times when an Oracle DBA needs to start a database process when an external event happens:

- Upon arrival of a redo log from another instance

- Upon arrival of an external file to an TEL feed

- Upon arrival of a new file for becoming an external table

- Upon arrival of a new object for BFILE inclusion

Before the Oracle file watcher utility, the DBA would have to write UNIX/Linux shell scripts to watch directories for new files, nohupping a daemon processes to sleep for a few seconds, and check for a condition, ad infinitum, until the condition is net.

Starting in Oracle 11g we see a new procedure in the dbms_scheduler package called dbms_scheduler.create_file_watcher that allows you to step outside of the database and execute code when a specific external event occurs.

Note:  The Oracle file watcher is not yet very sophisticated and it will only trigger code to execute upon the arrival of a new data file into the target directory.

Also note that there are limitations when the code execution time exceeds the arrival time for new files.  By default, the arrival of new files will be ignored if the triggered job is already running. If you need the job to fire for each new arrival, regardless of whether the job is already running or not, set parallel_instances=true.

Creating a Oracle file watcher

Oracle says that creating a file watcher involves these five steps.  Creating a file watcher is not immediately obvious and the steps are a tad convoluted:

1. Create a Credential

2. Create a File Watcher

3. Create a Program Object with a Metadata Argument

4. Create an Event-Based Job That References the File Watcher

5. Enable All Objects

Here are code samples for each of the five steps, per the Oracle docs: 

1.  Create a credential:

BEGIN

  DBMS_SCHEDULER.CREATE_CREDENTIAL('WATCH_CREDENTIAL', 'salesapps', 'sa324w1');

END;

/

2.  Create a file watcher

BEGIN

  DBMS_SCHEDULER.CREATE_FILE_WATCHER(

    FILE_WATCHER_NAME => 'EOD_FILE_WATCHER',

    DIRECTORY_PATH    => '?/eod_reports',

    FILE_NAME         => 'eod*.txt',

    CREDENTIAL_NAME   => 'WATCH_CREDENTIAL',

    DESTINATION       => NULL,

    ENABLED           => FALSE);

END;

/

3.  Create a program object:

BEGIN

  DBMS_SCHEDULER.CREATE_PROGRAM(

    PROGRAM_NAME        => 'DSSUSER.EOD_PROGRAM',

    PROGRAM_TYPE        => 'STORED_PROCEDURE',

    PROGRAM_ACTION      => 'EOD_PROCESSOR',

    NUMBER_OF_ARGUMENTS => 1,

    ENABLED             => FALSE);

END;

/

4.  Define the metadata argument using the event_message attribute.

BEGIN

  DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT(

    PROGRAM_NAME       => 'DSSUSER.EOD_PROGRAM',

    METADATA_ATTRIBUTE => 'event_message',

    ARGUMENT_POSITION  => 1);

END;

/

4.  Prepare an event-based job:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

    JOB_NAME        => 'DSSUSER.EOD_JOB',

    PROGRAM_NAME    => 'DSSUSER.EOD_PROGRAM',

    EVENT_CONDITION => NULL,

    QUEUE_SPEC      => 'EOD_FILE_WATCHER',

    AUTO_DROP       => FALSE,

    ENABLED         => FALSE);

END;

/

 

5.  Enable objects:

BEGIN

   DBMS_SCHEDULER.ENABLE('DSSUSER.EOD_PROGRAM,DSSUSER.EOD_JOB,EOD_FILE_WATCHER');

END;

/

You can view information about file watchers by querying dba_scheduler_file_watchers:

set linesize 100

column file_watcher_name format a20
column destination       format a15
column directory_path    format a15
column file_name         format a10
column credential_name   format a20

select
   file_watcher_name,
   destination,
   directory_path,
   file_name,
   credential_name
from
   dba_scheduler_file_watchers;

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.