Re: How to capture context from a database link?

From: Mark Bole <makbo_at_pacbell.net>
Date: Wed, 25 Aug 2004 00:50:30 GMT
Message-ID: <qnRWc.11838$v55.624@newssvr29.news.prodigy.com>

Galen Boyer wrote:
> On Mon, 23 Aug 2004, makbo_at_pacbell.net wrote:
>

>>Galen Boyer wrote:
>>
>>
>>>On 20 Aug 2004, jared_at_hwai.com wrote:
>>>
>>>
>>>>Galen Boyer <galenboyer_at_hotpop.com> wrote in message
>>>>news:<ullgaoq2c.fsf_at_standardandpoors.com>...
>>>>
>>>>
>>>>
>>>>>First things first.  This is wrong design.  You are putting
>>>>>intelligence in keys.  A definitely bad thing to do.  The
>>>>>domain needs to be an attribute on the table being inserted.
>>>>
>>>>No, not an intelligent key, just a way to guarantee uniqueness
>>>>when combining sequence-generated numbers from multiple
>>>>databases.
>>>
>>>Well, then why didn't you ask that?
>>>sys_guid()
>>>
>>
>>Doesn't work across a dblink.

>
>
> On the remote instance:
>
> CREATE OR REPLACE function sysguid
> RETURN varchar2
> AS
> v_sysguid varchar2(2000);
> BEGIN
> select cast(sys_guid() as varchar2(2000))
> into v_sysguid
> from dual
> ;
> RETURN v_sysguid;
> END;
> /
>
> On the local instance:
>
> variable v_sysguid varchar2(2000);
> exec :v_sysguid := sysguid_at_somedblink;
>

OK, I'll take your word for it! This (sys_guid) is one of the three approaches documented in Advanced Replication for creating unique sequence numbers from different databases.

The OP mentioned third-party schemas over which he had no control, so there is still the issue of where do you create this function, what grants and synonyms do you need to manage, etc. Also, from what I read, the SYS_GUID function is not reproducible (depends on the specific process that called it, and a platform-specific implementation), so it could be a little bit of over-kill (not reproducible in test cases).

I still like the GLOBAL_NAME approach, since it is built-in at the database level. The OP first tried to get the DB_DOMAIN value, and since GLOBAL_NAME will contain that value (by default, at the time the database was created), it seemed to me a close fit. But, There Is More Than One Way To Do It.

And thanks for pointing out the CAST function, another one of those things I Never Knew Or Forgot.

--Mark Bole Received on Tue Aug 24 2004 - 19:50:30 CDT