Re: How to capture context from a database link?

jared_at_hwai.com (Jared) wrote in message news:<480f610.0408190627.21b7234e_at_posting.google.com>...
> 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

Jh, perhaps since you have to have one select for each site you can just concatenate in the domain name in the select

select 'DOMAIN'||sequence.nextval ...?

HTH -- Mark D Powell -- Received on Thu Aug 19 2004 - 14:55:22 CDT