OracleAlerter Receiving One Alert for Multiple Records

[DavidBrower]

We are using the DevArt OracleAlerter to listen for alerts from an Oracle database. It works fine for a single update/delete/insert. However, when multiple records are deleted together we only receive one alert containing just one of the records.

Here is my PL/SQL:

Code: Select all

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 my C#:

Code: Select all

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
    }

I've also posted this question on StackOverflow and the Oracle forum:

https://community.oracle.com/thread/3928329
http://stackoverflow.com/questions/3713 ... le-actions

Is there something that I am doing wrong here?

[Shalex]

Please confirm that answers on StackOverflow and the Oracle forum explained the issue you have encountered.

[DavidBrower]

Yes, it looks like the Oracle alert package only sends one alert per transaction.

We've switched to polling...