Event Based Execution - File Arrival Example
By David Allan-Oracle on Sep 08, 2008
If you want to do truly event based execution based on arrival of a file there is a convenient package supplied with the Oracle database scheduler component that can be used, the package can be downloaded from OTN here.
For example if you have an OWB mapping that uses an external table as a source and you wish to execute the mapping when a file arrives on the filesystem (that the external table is dependent on), then you can do this a number of ways, here we will illustrate how the database scheduler is used to execute the map.
After the package otn_sched_file_watch has been installed from the Oracle scheduler download described earlier we can use it to execute any job we have privilege to. For example given the following mapping;
which uses the file in STAGING_DIR named geog.csv;
If we wanted to execute the mapping when geog.csv arrives on the file system then we can create a file watch schedule and then create a job, this is described in the readme file for the file watch package. For an 10gR2 mapping this would be like the following (11g would also include the set_workspace code);
job_action=>'declare v varchar2(4000);r varchar2(4000); begin load_geog0.main(v,''false'',r); end;',
The mapping is a PLSQL implemented mapping so we can use the MAIN procedure to execute the mapping (as defined in the job_action parameter), we could also have used the OWB runtime APIs to execute the map or any process flow for example based upon events like this. The action in this case is a PLSQL block as defiend by the job_type parameter. We use the schedule GEOGFILES in the job creation which was defined using the api otn_sched_file_watch.create_file_watch_schedule
The file wait package will poll every 5 minutes for a while, as defined in the documentation the dba can change the setting using s function (otn_sched_file_watch_int.set_file_watch_intv) or each job owner can alter the frequency interval using dbms_scheduler.set_attribute using attribute repeat_interval.
exec dbms_scheduler.set_attribute('OTN_SCHED$_FILE_WATCH_JOB', 'REPEAT_INTERVAL', 'FREQ=MINUTELY;INTERVAL=1');
will change the repeat interval to every minute, a bit too frequent but ok for demos and getting the environment setup. See the DBMS_SCHEDULER documentation for the syntax for this property.
As you can see this is fairly straightforward to leverage and allows you to move data upon events rather than time based schedules. There are also some ftp based routines that are worth checking out in dbms_scheduler, these could be embedded as pre-post mapping triggers for example all leveraging the components of the Oracle database.