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.

Current Setup

  • 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 DBMS_ALERT and 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.

  • You are really handcuffing yourself with all the things that cannot be changed. Have you considered Advanced queues, or a table that is inserted into every time a row is changed? – kevinsky Sep 19 '14 at 18:12
  • @kevinsky Believe me I know, I'm not handcuffing myself... I'm being handcuffed by the existing system, time, the old developers and upper management. I've looked at Advanced Queuing briefly but didn't know if it's supported by the old Oracle .net drivers. Do you happen to know if it is and maybe a place I can find a good simple example? I've also considered inserting into a table but like I said, I'm trying to minimize changes to the existing Oracle database. – vane Sep 19 '14 at 18:37
up vote 2 down vote accepted

This could be done this way. I don't like it much as it is not very elegant and looks fragile if unexpected things happen.

--1)Create a table in the user's schema or another new schema if you want separation.

CREATE TABLE DATA_AUDIT
( Primary_key RAW(16) default SYS_GUID() not null,
  Data_id  Number(10),
  Action   VARCHAR2(10),
  Date_created TIMESTAMP(6)DEFAULT CURRENT_TIMESTAMP);
--add foreign key constraint for Data_id to the parent Table
--add check constraint: Action in ('INSERT','UPDATE','DELETE');

--2) create a trigger on the parent table
CREATE OR REPLACE TRIGGER TGR_PARENT_TABLE
 BEFORE INSERT OR UPDATE OR DELETE
 ON PARENT_TABLE
 FOR EACH ROW
 v_action DATA_AUDIT.action%type;
BEGIN

    IF INSERTING THEN
       v_action := 'INSERT';
    ELSIF DELETING THEN
        v_action := 'DELETE';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSE
        --raise an error for an unexpected event and log it
        RAISE;
    END IF;
       Insert into DATA_AUDIT(Primary_key,Data_id, Action,Date_created) VALUES
       (SYS_GUID(), :old.Data_id, v_action,CURRENT_TIMESTAMP);
END;

--3) create a trigger on DATA_AUDIT
CREATE OR REPLACE TRIGGER TGR_DATA_AUDIT
 BEFORE INSERT
 ON DATA_AUDIT
 FOR EACH ROW

BEGIN

DBMS_ALERT.SIGNAL(:old.action, :old.data_id);
END;
  • This is what I came up with while trying to find a reliable and "elegant" solution. It's also the solution a coworker of mine came up with. I'll give this a shot on Monday when I get into work. – vane Sep 20 '14 at 4:24
  • This works so I'll accept this answer. However, for now I'm going to leave it as is since the only thing that modifies the tables is an Oracle Forms application and it doesn't ever do any multi-row affecting statements. I just wanted to account for all situations and if it becomes an issue I'll fully implement this. Thanks for the answer. – vane Sep 22 '14 at 15:02
  • Ah, Forms... You can do stored procedures in Forms and skip the triggers if you like. – kevinsky Sep 22 '14 at 15:13
  • haha I wish I could. Hands are tied there as well, I'm not allowed to modify the Oracle Forms app at all. This is really just a stop-gap solution and will be going away shortly. It's a legacy application that is being phased out but needs to work up until we get the new system up and running 100% – vane Sep 22 '14 at 15:26

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.