Hi,

I am struggling several days with following issue.

I am trying to implement event based job.
At start all was working fine.
But after several payload type modifications + several times recreated queue + recreated scheduled job ... scheduler isn't triggered by event queue anymore.

I see that records are stored in event queue.But scheduled job doesn't process them.

I tried to run this script in the same DB's new schema. I added all necessary grants for AQ + type + job creation

In fresh schema this script is working fine and queue is being processed by job.

Could i have broken some data dictionaries or something else?
I don't have any ideas what should i check now.

CREATE TABLE scheduler_test (
  id            NUMBER(10)    NOT NULL,
  created_date  DATE          NOT NULL,
  CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
/

CREATE SEQUENCE scheduler_test_seq;
/
select * from user_types;
select * from user_type_attrs;

/
CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
  event_name  VARCHAR2(30)
);
/
BEGIN
  -- Create a queue table to hold the event queue.
  DBMS_AQADM.create_queue_table(
    queue_table        => 'event_queue_tab',
    queue_payload_type => 't_event_queue_payload',
  MESSAGE_GROUPING => DBMS_AQADM.TRANSACTIONAL, 
    sort_list => 'COMMIT_TIME', 
    compatible => '10.1',
    multiple_consumers => true, 
    comment            => 'Queue Table For Event Messages');

  -- Create the event queue.
  DBMS_AQADM.create_queue (
    queue_name  => 'event_queue',
    queue_table => 'event_queue_tab');

  -- Start the event queue.
  DBMS_AQADM.start_queue (queue_name => 'event_queue');
END;
/
BEGIN
   DBMS_SCHEDULER.create_job (
      job_name        => 'event_based_job',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN
                            INSERT INTO scheduler_test (id, created_date)
                            VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
                            COMMIT;
                          END;',
      start_date      => SYSTIMESTAMP,
      event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
      queue_spec      => 'event_queue',
      enabled         => false);
   
    dbms_scheduler.set_attribute('event_based_job','parallel_instances',TRUE);   
 
 dbms_scheduler.enable('event_based_job');
END;
/
DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_queue_msg           t_event_queue_payload;
BEGIN
  l_queue_msg := t_event_queue_payload('give_me_a_prod');

  DBMS_AQ.enqueue(queue_name          => 'event_queue',
                  enqueue_options     => l_enqueue_options,
                  message_properties  => l_message_properties,
                  payload             => l_queue_msg,
                  msgid               => l_message_handle);
  COMMIT;
END;
/
SELECT * FROM scheduler_test;

cleanup script

-- Remove the job.
begin
   DBMS_SCHEDULER.drop_job('event_based_job');
  
-- Stop the event queue.
  DBMS_AQADM.stop_queue (queue_name => 'event_queue');

-- Drop the event queue.
  DBMS_AQADM.drop_queue (queue_name => 'event_queue');

-- Remove the queue table.
  DBMS_AQADM.drop_queue_table(queue_table => 'event_queue_tab');
end;
/
DROP TYPE t_event_queue_payload;
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;

PURGE RECYCLEBIN;