Database Software

Executive Summary: How do I get the EVENT_MESSAGE parameter from the event raised by the FILE_WATCHER, into a Chain Step?

I am trying to make a simple Chain that is supposed to

1. Wait for a file to arrive;
2. Load the file into a table;
3. Merge the loaded data into an aggregated table;
4. Stop.

I have a stored procedure:

procedure CDA_Load_Incoming_File (payload IN sys.scheduler_filewatcher_result) as
blah blah blah :)

The LOADINCOMINGFILE program is defined as

  dbms_scheduler.create_program(
    program_name        => 'LoadIncomingFile',
    program_type        => 'stored_procedure',
    program_action      => 'CDA_Load_Incoming_File',
    number_of_arguments => 1,
    enabled             => false
  );
  dbms_scheduler.define_metadata_argument(
    program_name        => 'LoadIncomingFile',
    metadata_attribute  => 'event_message',
    argument_position   => 1
  );

This works fine when the program is used directly as a Job target i.e.

  dbms_scheduler.create_job(
    job_name        => 'LOAD_ACS_watcher_job',
    program_name    => 'LoadIncomingFile',
    queue_spec      => 'ACS_USAGE_WATCHER',
    auto_drop       => false,
    enabled         => false);

Enabling this job works perfectly - the event_message gives the procedure the file name, and it gets loaded properly.

So I want to add another step after the file is loaded, to do the merge.

I disabled the 'LOAD_ACS_watcher_job' and created a Chain.

I couldn't see a way to link the File Watcher step to the program, so the first step in the Chain has the file watcher as the queue_spec according to the docs, and the second step refers to the Program (LOADINCOMINGFILE).

The Chain Job successfully waits until the File Watcher sees the file has arrived, and then everything is triggered correctly according to the Rules. However, I can't work out how to get the EVENT_MESSAGE parameter into the Load_File step so it knows which file to actually load - the second step always shows in the Job Run Details as FAILED:

CHAIN_LOG_ID="11592", STEP_NAME="LOAD_ACS_USAGE_STEP", ORA-01024: invalid datatype in OCI call
ORA-21560: argument 7 is null, invalid, or out of range

So my first two steps and the rules linking them are defined as follows:

    DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
        chain_name              => 'LOAD_ACS_USAGE_CHAIN',
        step_name               => 'ACS_USAGE_WATCHER_STEP',
        event_condition         => '',
        queue_spec              => 'ACS_USAGE_WATCHER'
    );

    DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
        chain_name              => 'LOAD_ACS_USAGE_CHAIN',
        step_name               => 'LOAD_ACS_USAGE_STEP',
        program_name            => 'LoadIncomingFile');

    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        CHAIN_NAME            => 'LOAD_ACS_USAGE_CHAIN',
        RULE_NAME            => 'SCHED_RULE$1',
        CONDITION            => 'TRUE',
        ACTION                => 'START "ACS_USAGE_WATCHER_STEP"'
    );

    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        CHAIN_NAME            => 'LOAD_ACS_USAGE_CHAIN',
        RULE_NAME            => 'SCHED_RULE$2',
        CONDITION            => '"ACS_USAGE_WATCHER_STEP" COMPLETED',
        ACTION                => 'START "LOAD_ACS_USAGE_STEP"'
    );

    DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
        CHAIN_NAME            => 'LOAD_ACS_USAGE_CHAIN',
        RULE_NAME            => 'SCHED_RULE$3',
        CONDITION            => '"LOAD_ACS_USAGE_STEP" COMPLETED',
        ACTION                => 'START "MERGE_ACS_USAGE_STEP"'
    );

I can't see how to execute code in the File Watcher step, which is (I think) the basic problem since this is where the EVENT_MESSAGE parameter would/should be available.

What am I missing? I need to get the file-name into the stored procedure, but can't see how it should be done.

[Edit:]  @GregV Sorry - for some reason I can't reply to your reply, so I'll have to do it this way.

1. Yes, I could combine the steps, but (a) this would be more flexible if it works (i.e. sometimes there may be no merge, etc), and (b) I'm using this as an opportunity to learn about Job Chains;

2. Without the Start Rule the Chain comes up CHAIN_STALLED, which I believe means nothing will ever happen;

3a. The File Watcher has a pattern for the file-name "details_*.csv" since the files are named with dates (e.g. details_20160517_180320.csv), so the file-name isn't actually in the File Watcher;

3b. Possibly I was obtuse - the problem is that I can't get hold of the SCHEDULER_FILEWATCHER_RESULT from the event, or at least that I can't see where to get it from either as part processing the File Watcher step, or the subsequent step - I can't refer to a Program in DEFINE_CHAIN_EVENT_STEP, and I can't see a way to set metadata_argument for anything except a Program. I suppose another way to phrase the question would be "How do I execute a stored procedure in a CHAIN_EVENT_STEP"?

Also - I forgot to say - I'm on Windows 7, running "Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production"