Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Raivis.

Asked: September 27, 2019 - 9:31 am UTC

Answered by: Chris Saxon - Last updated: November 12, 2019 - 12:58 pm UTC

Category: PL/SQL - Version: 11g R2

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: How to get compiler settings for PL/SQL program units

You Asked

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;

and we said...

But after several payload type modifications + several times recreated queue + recreated scheduled job ... scheduler isn't triggered by event queue anymore.

Are you doing all that because the queue isn't triggered?

Or have those actions caused the queue to stop?

In any case, what exactly did you do?

I've been running your test case for a while now with no issues. Your test case is otherwise excellent, but without the actions that's making the queue fail I'm at a loss here.

If you can share with us the steps that resulted in the queue failing we may be able to get to the bottom of this.

But if it's repeatedly happening with no action on your part, this is one for support.

and you rated our response

  (4 ratings)

Reviews

Records from queue are not removed

October 29, 2019 - 10:14 am UTC

Reviewer: Raivis Voitkuns

Hi,

Thanks for response.
I have one more question regarding my example.

When "event_based_job" job process submitted event, the event isn't removed from the queue table - "event_queue_tab". Do I need add some additional functionality to my job, so after successful insert into "scheduler_test" table event would be removed from queue table "event_based_job".

BR,
Raivis Voitkuns
Chris Saxon

Followup  

November 01, 2019 - 9:20 am UTC

The job should dequeue the messages automatically.

Is there another consumer configured for this queue? How are you verifying the that messages you're viewing have been dequeued by the job?

Advance Queue error...

November 11, 2019 - 10:45 am UTC

Reviewer: Raivis Voitkuns

In production environment records were dequeued automatically.

But today we faced with another strange problem.
We have several DB servers in production and in one server queue mechanism is not working anymore.

When I try to select from queue table I am getting ORA-22636.
I read this can be caused due memory problems or tablespace size...

Is that true?
If yes, how we can restart or resume queue, so it can work again?

BR,
Raivis

Chris Saxon

Followup  

November 11, 2019 - 2:03 pm UTC

Not sure if this is your issue, but MOS note 1551003.1 suggests:

SQL> select * from v$timezone_file;

When v$timezone_file differ between the sites, then the version information is likely the cause.

This is due to timezone file version discrepancy, check the version from the source DB and destination DB

indeed will have different version,then upgrade timezone file to resolve the issue.


If that doesn't help, contact support.

November 11, 2019 - 2:17 pm UTC

Reviewer: Raivis Voitkuns

I am a little bit confused.
Queue and dequeue is happening in the same DB, I don't specify anywhere DB links.
Chris Saxon

Followup  

November 12, 2019 - 11:37 am UTC

My mistake, when you mentioned several databases I thought the queues were between the databases.

In any case, the advice still stands:

Contact support!

November 12, 2019 - 12:54 pm UTC

Reviewer: Raivis Voitkuns

We changed timezone and restarted DB, today queue is working without any problems.

That means somehow queue compared timezone values... but I don't between what DB then it compared.


Chris Saxon

Followup  

November 12, 2019 - 12:58 pm UTC

Are you sure it's changing the timezone file that did the trick? Or could it be because you restarted the DB?

More to Explore

Scheduler

All of the database utilities including the Scheduler are explained in the Utilities guide.