Introduced in Oracle Database 11g Release 2, file watchers allow triggering jobs when a new file is created in a predefined operating system path. A file watcher can watch for a file on the local system or a remote system. As prerequisite to use file watchers, the database Java virtual machine (JVM) component must be installed.

A quick example

Let’s create a new database user and grant it all the privileges needed to run our file watcher example:

SQL> CREATE USER z_fwatcher IDENTIFIED BY fwatcher123
  2  DEFAULT TABLESPACE USERS;

User created.

SQL> GRANT CREATE SESSION TO z_fwatcher;

Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO z_fwatcher;

Grant succeeded.

SQL> GRANT CREATE TABLE TO z_fwatcher;

Grant succeeded.

SQL> GRANT CREATE JOB TO z_fwatcher;

Grant succeeded.

SQL> GRANT CREATE ANY DIRECTORY TO z_fwatcher;

Grant succeeded.

SQL> GRANT CREATE EXTERNAL JOB TO z_fwatcher;

Grant succeeded.

SQL> ALTER USER z_fwatcher QUOTA UNLIMITED ON users;

User altered.

Create the database directory to be used as working area:

SQL> CREATE OR REPLACE DIRECTORY FW_DIR AS '/tmp/file_watcher';

Directory created.

Now create the operating system directory:

SQL> !mkdir /tmp/file_watcher

Grant database directory access to user Z_FWATCHER:

SQL> GRANT read, write ON DIRECTORY fw_dir TO z_fwatcher;

Grant succeeded.

We need an operating system account to access files, for this purpose we will use a credential object. Credentials are database objects that hold a username/password pair for authenticating callout functions.

SQL> BEGIN
  2    DBMS_SCHEDULER.create_credential(
  3      credential_name => 'fw_credential',
  4      username        => 'oracle',
  5      password        => 'nueva123');
  6  END;
  7  /

PL/SQL procedure successfully completed.

Now create the scheduler object as follows. In this example, all files created in ‘/tmp/file_watcher’ directory will be monitored:

SQL> BEGIN
  2    DBMS_SCHEDULER.create_file_watcher(
  3      file_watcher_name => 'z_test_FW',
  4      directory_path    => '/tmp/file_watcher',
  5      file_name         => '*',
  6      credential_name   => 'fw_credential',
  7      destination       => NULL,
  8      enabled           => FALSE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

The procedure to be used as example will insert the file name into a table. Let’s create the table and the store procedure:

SQL> CREATE TABLE z_fwatcher.file_watcher_example (
  2    file_content VARCHAR2(200)
  3  );

Table created.

SQL> CREATE OR REPLACE PROCEDURE
  2  z_fwatcher.fw_test_code (sfw_result SYS.SCHEDULER_FILEWATCHER_RESULT)
  3  AS
  4    line_in file_watcher_example.file_content%TYPE;
  5  BEGIN
  6    line_in := sfw_result.actual_file_name || ' FOUND!';
  7    INSERT INTO file_watcher_example
  8    VALUES (line_in);
  9    COMMIT;
 10  END;
 11  /

Procedure created.

A scheduler program referencing the ‘event_message’ metadata attribute is necessary to execute our database store procedure:

SQL> BEGIN
  2    DBMS_SCHEDULER.create_program(
  3      program_name        => 'Z_FWATCHER.FW_TEST_PROGRAM',
  4      program_type        => 'stored_procedure',
  5      program_action      => 'Z_FWATCHER.FW_TEST_CODE',
  6      number_of_arguments => 1,
  7      enabled             => FALSE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_SCHEDULER.define_metadata_argument(
  3      program_name       => 'Z_FWATCHER.FW_TEST_PROGRAM',
  4      metadata_attribute => 'event_message',
  5      argument_position  => 1);
  6  END;
  7  /

PL/SQL procedure successfully completed.

Finally, create the file watcher test job as follows:

SQL> BEGIN
  2    DBMS_SCHEDULER.create_job(
  3      job_name        => 'FW_TEST_JOB',
  4      program_name    => 'Z_FWATCHER.FW_TEST_PROGRAM',
  5      event_condition => NULL,
  6      queue_spec      => 'z_test_FW',
  7      auto_drop       => FALSE,
  8      enabled         => FALSE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

You can now enable all the objects created:

SQL> EXEC DBMS_SCHEDULER.enable('z_test_FW');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SCHEDULER.enable('Z_FWATCHER.FW_TEST_PROGRAM');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SCHEDULER.enable('FW_TEST_JOB');

PL/SQL procedure successfully completed.
Query the output table, at this time the table is empty:

SQL> SELECT * FROM z_fwatcher.file_watcher_example;

no rows selected

Create a new file called “file_1.fw” in the directory defined as file watcher working area:

SQL> !touch /tmp/file_watcher/file_1.fw

The file is detected and our database store procedure is executed. Once processed you can query the example table again:

SQL> SELECT * FROM z_fwatcher.file_watcher_example;

FILE_CONTENT
--------------------------------------------------------------------------------
file_1.fw FOUND!

This time, the table shows a record corresponding with the file processed.

TIP

You can change the default file watcher checking time to 1 minute as follows:

SQL> BEGIN
  2    DBMS_SCHEDULER.set_attribute(
  3      'file_watcher_schedule',
  4      'repeat_interval',
  5      'freq=minutely; interval=1');
  6  END;
  7  /

PL/SQL procedure successfully completed.

Comment