Hi,

I'm having a problem of architecture nature and due to time constraints
and having little knowledge about Oracle I need your help. Thanks for
any suggestions.

Basically I need to develop an app that runs on windows platform and
it's responsible for triggers management on oracle. So when required
it'd create, modify and delete triggers. From what I've read the
OCI would be an ideal choice, I could remotely connect to Oracle and
give it specific commands. Am I right? What do you think? What sort of
privileges does user need to manage triggers?

There's a next part. All triggers created on Oracle by my app have to
notify my server running on a remote machine when fired. I also would
like to pass data to the server e.g. for delete triggers row(s) that
have been deleted. I imagine process for e.g. delete trigger might look
like the following:

Trigger body
{
Open up a connection to the server

For each row deleted
pass data to the server

Close the connection
}


I'd like to connect to the server using TCP/IP from within a trigger.


The only way I know about is to create a package with java functions
that can establish a connection, pass data and close the connection at
the end. Is it possible to keep a 'session' when using functions
from a package?

Maybe there is a better technology to use. What do you think?

Please help thanks

Best regards,
schw

You can do this in Java should you choose. Have you looked at the
supplied UTL_TCP package to see if it meets your needs?

Additionally, it sounds like what you are building could benefit from
Advanced Queueing (AQ). Your trigger can place a message for your server
in the queue and then the server comes by and consumes those messages.
I'm not sure if it fits the bill for you or not, but you might want to
look into it.

Cheers,
Brian


--
================================================== =================

Brian Peasland
oracle_dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

schw wrote:
I think you posted insufficient information from which to help you.

What is the business case?
What is the Oracle version?
What kind of trigger? (table, instead-of, DDL event, system event)

Daniel Morgan
www.psoug.org