OracleAlerter Receiving One Alert for Multiple Records

OracleAlerter Receiving One Alert for Multiple Records

Postby DavidBrower » Tue 10 May 2016 14:28

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/37136333/oracle-dbms-alert-signal-firing-once-for-multiple-actions

Is there something that I am doing wrong here?
DavidBrower
 
Posts: 4
Joined: Tue 10 May 2016 14:20

Re: OracleAlerter Receiving One Alert for Multiple Records

Postby rianna » Thu 12 May 2016 11:09

I get almost the same alert...Still waiting for an answer.
Check the full list of Dixie Chicks concert dates that is gong to happen this year.
Last edited by rianna on Mon 30 May 2016 13:56, edited 1 time in total.
rianna
 
Posts: 3
Joined: Wed 17 Feb 2016 20:06

Re: OracleAlerter Receiving One Alert for Multiple Records

Postby Shalex » Thu 12 May 2016 11:47

Please confirm that answers on StackOverflow and the Oracle forum explained the issue you have encountered.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: OracleAlerter Receiving One Alert for Multiple Records

Postby DavidBrower » Tue 17 May 2016 08:22

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

We've switched to polling...
DavidBrower
 
Posts: 4
Joined: Tue 10 May 2016 14:20


Return to dotConnect for Oracle