Oracle dbms_alert.signal firing once for multiple actions

Question

I have a trigger in Oracle that calls sys.dbms_alert.signal. A DevArt OracleAlerter receives these alerts in a self-hosted service.

However, I am finding that even for multiple deletes/updates/inserts the OracleAlerter is receiving an alert for just one of the records rather than one alert for all of the records.

Is there a way to view a history or log of the signals that have been fired in Oracle (11g)?

This is an example of the type of trigger that is sending the alert:

create or replace trigger MACL.ZM_SOCOMM_trigger
after delete or insert or update on MACL.SOCOMM
for each row
declare
   alertData varchar2(1000);
begin

alertData := '{"Schema":"MACL","Table":"SOCOMM","Index":"I_SOCOM_KEY","Columns":[{"COLUMN_NAME":"SOCOM_ORDREF","COLUMN_POSITION":1,"COLUMN_VALUE":"#SOCOM_ORDREF#"},{"COLUMN_NAME":"SOCOM_ORDLINE","COLUMN_POSITION":2,"COLUMN_VALUE":"#SOCOM_ORDLINE#"},{"COLUMN_NAME":"SOCOM_COMSEQ","COLUMN_POSITION":3,"COLUMN_VALUE":"#SOCOM_COMSEQ#"},{"COLUMN_NAME":"SOCOM_COMTYPE","COLUMN_POSITION":4,"COLUMN_VALUE":"#SOCOM_COMTYPE#"},{"COLUMN_NAME":"SOCOM_TEXT","COLUMN_POSITION":5,"COLUMN_VALUE":"#SOCOM_TEXT#"}],"ChangePlaceHolder":"#CHANGE#"}';

IF INSERTING OR UPDATING THEN
    alertData := replace(alertData, '#SOCOM_ORDREF#', :new.SOCOM_ORDREF);
    alertData := replace(alertData, '#SOCOM_ORDLINE#', :new.SOCOM_ORDLINE);
    alertData := replace(alertData, '#SOCOM_COMSEQ#', :new.SOCOM_COMSEQ);
    alertData := replace(alertData, '#SOCOM_COMTYPE#', :new.SOCOM_COMTYPE);
    alertData := replace(alertData, '#SOCOM_TEXT#', :new.SOCOM_TEXT);
ELSIF DELETING THEN
    alertData := replace(alertData, '#SOCOM_ORDREF#', :old.SOCOM_ORDREF);
    alertData := replace(alertData, '#SOCOM_ORDLINE#', :old.SOCOM_ORDLINE);
    alertData := replace(alertData, '#SOCOM_COMSEQ#', :old.SOCOM_COMSEQ);
    alertData := replace(alertData, '#SOCOM_COMTYPE#', :old.SOCOM_COMTYPE);
    alertData := replace(alertData, '#SOCOM_TEXT#', :old.SOCOM_TEXT);
END IF;

IF INSERTING THEN
    alertData := replace(alertData, '#CHANGE#', 'INSERT');
ELSIF DELETING THEN
    alertData := replace(alertData, '#CHANGE#', 'DELETE');
ELSE
    alertData := replace(alertData, '#CHANGE#', 'UPDATE');
END IF;

sys.dbms_alert.signal('ooalert_sync', alertData);
END;

Here is the code that I am using to subscribe to signals fired by Oracle:

    public void SetUpAlerts() => RegisterHandlers(CreateAlerter());

    private static void RegisterHandlers(OracleAlerter alerter)
    {
        alerter.Alert += AlerterOnAlert;
        alerter.Error += AlerterOnError;
        alerter.WaitTimeout += AlerterOnWaitTimeout;
        alerter.Stopped += AlerterOnStopped;

        alerts.Add(alerter);
        alerter.Start();
    }

    private OracleAlerter CreateAlerter() => new OracleAlerter
    { 
        Timeout = Day,
        AlertName = "Name",
        Connection = Factory.CreateSourceConnection() as OracleConnection
    };

    private static void AlerterOnAlert(object sender, OracleAlerterAlertEventArgs args)
    {
        //handle alert
    }

EDIT

I've added a statement in the trigger that inserts some data into an audit table every time the trigger fires and it appears that the trigger is being fired the correct number of times.

To clarify, you're running a single insert statement that updates multiple rows. The information for only one of those rows is being alerted on? Can you please post a minimal example of your Oracle trigger that demonstrates this issue? — Ben, May 10 '16 at 11:27

score 2 · Accepted Answer · answered May 11 '16 at 01:03

2

According to the documentation:

Alerts can be signalled more often than the corresponding application wait calls. In such cases, the older alerts are discarded. The application always gets the latest alert (based on transaction commit times).

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_alert.htm#ARPLS65178

answered May 11 '16 at 01:03

Jeffrey Kemp

59,135
14
106
158

Thanks, Jeffrey. Back to the drawing board! – David Brower May 11 '16 at 08:23

Oracle dbms_alert.signal firing once for multiple actions

1 Answers1