Unanswered: How to Launch an Oracle SP from SQL Trigger?
I need to know a few things about this..
1. What do I need running or available on SQL Server machine (presuming an Oracle client)
2. Has anyone done this and perhaps can give me some quidance?
3. Any concerns about going from outside a firewall to the Oracle instance?
Architecture is probably going look like this:
SQL -> ON INSERT Trigger -> business logic -> Call Oracle Procedure
Do NOT try to create this Frankenstein monster of database architecture.
The scope of triggers should be limited to their own table if possible, and to their own database at the most. Otherwise, you risk your database stability and data integrity on a chain of technologies stretching across multiple database engines and right through your network.
This is NOT a good idea, and trying to do this sort of thing is what has given triggers a bad name over the years.
I encourage you, instead, to use a trigger on your table to populate a staging table, and then running a frequent batch process (scheduler can run once per minute) to process the data in the staging table and make whatever external calls to the Oracle database are necessary.
The big advantage of this setup is that if you network connection fails or your Oracle database goes down, your SQL Server database is unaffected and continues running merrily along loading data into the staging table, which can then be processed once all systems are up and working again with no loss of data and no loss of service on your SQL Server database.
If it's not practically useful, then it's practically useless.
Blindman, thanks. I appreciate what you said and it makes sense. Ok, so no trigger other than to populate and use a storage table with the salient records.
What mechanisms exist to push this data into an Oracle table? That is the really important part because I do not want Oracle to have to pull that data. I know Oracle has transparent gateways and access managers for manipulating other data sources. Where do I go to make the same sorts of things happen for SQL server? My biggest constraint is that the data must be no older than a few seconds for this application is analyzing utility data for electrical outages. Optimally I could populate the salient records to an oracle resident table and the oracle side can handle itself (I already have that side built but I am having to "punt" due to problems with an SNMP tool failure that indicates the need for a better, more straight forward architecture).