Oracle: Event is not delivered to chain event step (DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP)

Question

I set up a Scheduler chain to test chain event step's reaction to events.
The events (messages) are not delivered.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Any ideas?

Thanks, Wolfgang

I defined a DBMS_AQADM queue wb_event_queue with queue table wb_event_queue_tab for multiple consumers and the following type for the queue table.

CREATE OR REPLACE TYPE wb_event_msg_type AS OBJECT (  
  name    VARCHAR2(100),  
  result  NUMBER(5)  
);  

The chain consists of 2 steps only. The first one (defined with DEFINE_CHAIN_STEP) enqueues a message with 'SCHEDULER$_EVENT_AGENT' in the recipient list

l_message_properties.recipient_list(0):=sys.aq$_agent(''SCHEDULER$_EVENT_AGENT'', null, null);

The second step (DEFINE_CHAIN_EVENT_STEP) starts when first step is completed in order to pick up the event from first step.

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
  chain_name => 'wb_chain_event_step'
  , step_name => 'wb_step_dequeue'
  , event_condition => 'tab.user_data.result=0'.
  , queue_spec => 'wb_event_queue'
);

I ran the chain via

begin --run chain
  DBMS_SCHEDULER.RUN_CHAIN(
    chain_name => 'wb_chain_event_step'
    , job_name => 'wb_job_event_step'
    , start_steps => 'wb_step_enqueue'
  );
end;
/

However, the message hangs in message table.
Consumer with name 'SCHEDULER$_EVENT_AGENT' waits at event queue.
The job wb_job_event_step does not finish.

Code to set up.

CREATE OR REPLACE TYPE wb_event_msg_type AS OBJECT (  
  name    VARCHAR2(100),  
  result  NUMBER(5)  
);  
/  

begin --create queue
  DBMS_AQADM.create_queue_table (
    queue_table => 'wb_event_queue_tab'
    , queue_payload_type => 'wb_event_msg_type'
    , multiple_consumers => true
  );

  DBMS_AQADM.create_queue (
    queue_name => 'wb_event_queue'
    , queue_table => 'wb_event_queue_tab'
  );

  DBMS_AQADM.start_queue (
    queue_name => 'wb_event_queue'
    , enqueue => true
    , dequeue => true
  );
end;
/


declare --create chain
  my_job_action varchar2(4000) := ''
||'DECLARE'
||'  l_enqueue_options DBMS_AQ.enqueue_options_t;'
||'  l_message_properties DBMS_AQ.message_properties_t;'
||'  l_message_handle RAW(16);'
||'  l_event_msg wb_event_msg_type;'
||'BEGIN'
||'  l_message_properties.recipient_list(0) := sys.aq$_agent(''SCHEDULER$_EVENT_AGENT'', null, null);'
||'  l_event_msg := wb_event_msg_type(''wolfgang'', 0);'
||'  DBMS_AQ.enqueue(queue_name          => ''wb_event_queue'', '       
||'                  enqueue_options     => l_enqueue_options,     '
||'                  message_properties  => l_message_properties,   '
||'                  payload             => l_event_msg,             '
||'                  msgid               => l_message_handle);'
||''
||'END;'
;
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
     program_name => 'wb_prog_result2event'
     , program_type => 'PLSQL_BLOCK'
     , program_action => my_job_action
     , number_of_arguments => 0
     , enabled => true
  );

  DBMS_SCHEDULER.CREATE_CHAIN(chain_name => 'wb_chain_event_step'); 

  DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
     chain_name => 'wb_chain_event_step'
     , step_name => 'wb_step_dequeue'
     , event_condition => 'tab.user_data.result=0'
     , queue_spec => 'wb_event_queue'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'wb_chain_event_step'
    , step_name => 'wb_step_enqueue'
    , program_name => 'wb_prog_result2event'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name  =>'wb_chain_event_step'
    , condition =>'true'
    , action    =>'start wb_step_enqueue'
    , rule_name =>'wb_rule_enqueue'
  ); 
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name  =>'wb_chain_event_step'
    , condition =>'wb_step_enqueue COMPLETED'
    , action    =>'start wb_step_dequeue'
    , rule_name =>'wb_rule_dequeue'
  ); 
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name  =>'wb_chain_event_step'
    , condition =>'wb_step_dequeue COMPLETED'
    , action    =>'end'
    , rule_name =>'wb_rule_end'
  ); 
  DBMS_SCHEDULER.ENABLE('wb_chain_event_step');

end;
/

Code to clean up.

execute dbms_scheduler.stop_job(job_name=>'wb_job_event_step');

begin --drop chain
  DBMS_SCHEDULER.DROP_CHAIN_RULE(
    chain_name => 'wb_chain_event_step'
    , rule_name=>'wb_rule_enqueue'
  );
  DBMS_SCHEDULER.DROP_CHAIN_RULE(
    chain_name => 'wb_chain_event_step'
    , rule_name=>'wb_rule_dequeue'
  );
  DBMS_SCHEDULER.DROP_CHAIN_RULE(
    chain_name => 'wb_chain_event_step'
    , rule_name => 'wb_rule_end'
  );
  DBMS_SCHEDULER.DROP_CHAIN_STEP(
    chain_name => 'wb_chain_event_step'
    , step_name => 'wb_step_enqueue'
  );
  DBMS_SCHEDULER.DROP_CHAIN_STEP(
    chain_name => 'wb_chain_event_step'
    , step_name => 'wb_step_dequeue'
  );
  DBMS_SCHEDULER.DROP_PROGRAM(
    program_name => 'wb_prog_result2event'
  );
  DBMS_SCHEDULER.DROP_CHAIN(
    chain_name => 'wb_chain_event_step'
  );
end;
/

begin --drop queue
  DBMS_AQADM.stop_queue (
    queue_name => 'wb_event_queue'
  );
  DBMS_AQADM.drop_queue(
    queue_name => 'wb_event_queue'
  );
  DBMS_AQADM.drop_queue_table(
    queue_table => 'wb_event_queue_tab'
end;
/

drop type wb_event_msg_type;
/