| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Who's calling who??
Jake wrote:
> I need a pointer
>
>
>
> I have the following setup:
>
>
>
> Box1 Box2
>
> ---- ----
>
> c1-- db1 --dblink-- db3
>
> c2-- db2 --dblink --/
>
>
> that is box1 has 2 databases, each having dblinks to db3. Clients can
> connect to either db on box1. In db1 and db2 there is a view which points
> to db3 (create view myview as select * from remotetable_at_db3). On
> remotetable there is a before insert trigger. I'd like to capture which db
> the insert originated at and stick that value in the remotetable.
>
> I can't change the code on db1 or db2!
>
> How can I determine the dbname (or anything else indicative) for the calling
> db. Using SYS_CONTEXT('USERENV','IP_ADDRESS') I can determine the IP
> address of the caller, but that doesn't help me because there are more than
> one database at the IP. All the other sys_context attributes give me info
> from db3.
>
> Are there some other sql functions, pl/sql function I can use?
> Help?
>
> Thanks
> Jake
>
>
>
Here's one I used to check ownership of media where the user could be accessing through a link. It uses the user function which returns the user session from the calling instance:
TRIGGER MEDIA_DBA.T_PHYS_MED_OWNER
BEFORE UPDATE
ON physical_medium
declare
cursor get_owner is
select owner from physical_medium
where upper(owner) = user
and
medium_id = :MEDIUM_ID;
At least as I recall it worked. Doesn't give you the database name but maybe you could set usernames up to embed the dbname.
Good place for questions like this is Revealnet's PL/SQL pipelines (now at Quest but the revealnet.com link still works.
fdp
 |
 |