How to capture context from a database link?

I have an interesting problem: I am writing a small function to generate a surrogate key. The function will be part of a statement populating a table with data from four different databases (identically structured, located in four geographic sites). Each database is part of a different domain. I will use four separate SQL*Plus queries (or PL/SQL functions) to populate said table, identical except for the database links used. I want a subset of the domain value to be part of the surrogate key.

From sys_context it is easy to get the db_domain value, but that is only for the database one is connected to. Does anyone know how to get the context of a database link in the 'from' clause? At this point, rather than SQL*Plus, I am thinking I will have to use PL/SQL to control the process and loop through four times, each time changing the context to the db_domain I want to use (so I can populate the surrogate key correctly).

Is there a more efficient way to do this?

TIA - Kind regards,
jh Received on Thu Aug 19 2004 - 09:27:25 CDT