Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    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

    Any help is appreciated, thanks!

    JTGIS

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2006
    Posts
    2
    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).

    Any pointers would be appreciated.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To send the data to Oracle, use either a DTS package or the OPENQUERY function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •