Let me state up front that I'm not proficient in Oracle at all, MS SQL yes but not Oracle; which is why I'm asking this.
- Oracle 10g Release 1 running on Red Hat Ent. 3 (no support)
- Can not upgrade OS or Database
- Can not extensively modify Database (read-only would be preferred)
- Using Visual Studio 2013 and .net 4.5 for data access to Oracle
- Using ODAP/ODP.NET 11.2 Release 5 (can not be newer due to using Oracle 10g R1)
- Using System.Data.Oracle instead of the ODP.NET classes to avoid file bloat
- Can not use Data Change Notification because it isn't supported in 10g R1
Under this current setup I can easily connect to the database, issue sql statements and read/write data. The application being written is to be used as a sort of adapter or bridge between the Oracle database and MongoDB where only a couple tables are meant to replicate changes into MongoDB.
The current implementation of this uses
triggers to communicate events into the .net application at which point it will read the data, convert it and write it into MongoDB.
Here is an example trigger that is being used
CREATE OR REPLACE TRIGGER EXAMPLE.TABLENAME_ALERT AFTER UPDATE OR INSERT OR DELETE ON EXAMPLE.TABLENAME FOR EACH ROW BEGIN IF INSERTING THEN DBMS_ALERT.SIGNAL('DATA_INSERTED', :new.data_id); DBMS_OUTPUT.PUT_LINE('INSERTED'); ELSIF UPDATING THEN DBMS_ALERT.SIGNAL('DATA_UPDATED', :old.data_id); DBMS_OUTPUT.PUT_LINE('UPDATED'); ELSIF DELETING THEN DBMS_ALERT.SIGNAL('DATA_DELETED', :old.data_id); DBMS_OUTPUT.PUT_LINE('DELETED'); END IF; END;
If I issue the following SQL statements:
update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 1; update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 2; update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 3;
The result is getting 3 separate
DATA_UPDATED signals and 3 separate
UPDATED lines in the database output. On the other hand, if I issue this statement:
update EXAMPLE.TABLENAME set example_column = 'test' where data_id <= 3;
The result I get is 1
DATA_UPDATED signal that contains the id of the last row updated and 3 separate
UPDATED lines in the database output. I need to get all 3 signals.
My guess is, that because
DBMS_ALERT.SIGNAL doesn't fire until a transaction is committed, only fires the most current signal at that time and a single batch update/insert/delete is considered 1 transaction then all I will ever receive is 1 signal from the database.
Is there a way I can get the multiple calls per trigger to
DBMA_ALERT.SIGNAL to actually send all the signals? Or perhaps there is a better way for me to detect changes to data in the database from an external application?
EDIT I know this implementation has a code smell, but my hands are tied, I have to work with what we have and can't really change anything.