OracleAlerter Receiving One Alert for Multiple Records

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
DavidBrower
Posts: 4
Joined: Tue 10 May 2016 14:20

OracleAlerter Receiving One Alert for Multiple Records

Post by 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/3713 ... le-actions

Is there something that I am doing wrong here?

rianna
Posts: 3
Joined: Wed 17 Feb 2016 20:06

Re: OracleAlerter Receiving One Alert for Multiple Records

Post by 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.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: OracleAlerter Receiving One Alert for Multiple Records

Post by Shalex » Thu 12 May 2016 11:47

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

DavidBrower
Posts: 4
Joined: Tue 10 May 2016 14:20

Re: OracleAlerter Receiving One Alert for Multiple Records

Post by 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...

Post Reply