CODEWITHSUNDEEP
oracle-databaseplsqlnotificationsoracle12c
tonyf
tonyf

Reputation: 35557

Oracle DBMS_SCHEDULER job to monitor a DBMS_ALERT

Env: Oracle 12c R2

Trying to understand what the best approach would be to set up an Oracle DBMS_SCHEDULER job that would be used to monitor a DBMS_ALERT trigger that checks when a specific column value changes within a table.

The thing is, this table column value change will sometimes occur on a frequent basis and sometimes it may only occur twice a day but I will need to monitor this column change via the DBMS_ALERT.

The trigger I have is as follows and I have a procedure called check_signal that checks for the signal that I wish to use within the DBMS_SCHEDULER job.

The goal that I am trying to achieve is that I am going to have the situation where I will need to run say, three jobs:

Job1
Job2
Job3

The thing is, the payload returned from Job1 is required and and passed as parameters into Job2 and again, the payload returned from Job2 is required and passed as parameters into Job3.

It is this wait/alert that I am trying to achieve through the use of DBMS_ALERTS.

create or replace trigger my_tab_upd after update of status on my_tab for each row
begin
   dbms_alert.signal('mystatusalert', 'changed from '||:old.status||' to '||:new.status||'.');
end;
/

This will be used via a web-based application which is used by multiple users.

Just unsure on how to setup this scheduled job that will continuously check for the alert and then be used within the web app.

If there is a better means than DBMS_ALERT, then please let me know.

Upvotes: 0

Views: 448

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21073

The general answer is simple, while polling for events every N seconds you get an average delay N/2 seconds and maximal delay of N seconds.

In context of DBMS_ALERT you should re-think this approach, as this will implement polling with wait on the event.

The periodically executed jobs make basically tho thinks:

  • DBMS_ALERT.REGISTER on an event name

  • wait with DBMS_ALERT.WAITONE

Assume that the DBMS_SCHEDULER jobs runs every 10 seconds and it is started in the phase with frequent signalling. So the first execution returns quickly after receiving an event.

The second execution falls in the quite period, so the job will wait hours to get an event.

I think this is not what you expect as

1) the waiting job will have an open session - what you want to avoid as follows from you other question

You may use timeout = 0 in the DBMS_ALERT.WAITONE, but this will return close to no events, except those fired accidentally between the REGISTER and WAITONE

2) if in the first 10 seconds two events are signalled, the second one will be lost as at the signaling time the subscribing job is not active and no registration exists.

Upvotes: 1

Related Questions