Re: Determining calling database via dblink

"Jake" <me_at_heyjay.com> wrote in message news:<akegk8$bjl$1_at_bob.news.rcn.net>...
> Hi,
>
> I have a setup where I have 2 remote databases and a local database. I have
> table1 on my local database, table1 has a before insert trigger which calls
> a function. My remote databases have views which are simple select * from
> table1_at_local_db.
>
> How can I determine which database is inserting to table1?
>
> Ideally, I'd like to use something like sys_context and get the global_name
> of the database which initiated the transaction. I thought of using
> SYS_CONTEXT ('USERENV','IP_ADDRESS'), but then that will require I keep a
> list of IP addresses and do the crossref, and if we move a server I'll
> forget and my app will blow up. Furthermore (since I haven't had a chance
> to test this yet) I don't even know if I'll get the remote db's IP in this
> situation.
>
> Any ideas? Help?
>
> Thanks
> J

J, why do you really care where the insert came from?

If you really need to track this then are you using database links that contain an imbedded userid and password. If you are then setup a different userid/password combination for each link so now your insert trigger can capture the Oracle userid and that tells you where the insert came from and would not change with db server relocation.

Otherwise I would go back to looking at what sys_context gives you.

HTH -- Mark D Powell -- Received on Tue Aug 27 2002 - 08:22:52 CDT