Re: Variable database link in cursor

Don A. <dba_at_direct.ca> wrote in
news:8d0p3v4los3vl0dlrm70cubjvrlbtt58e2_at_4ax.com:

> Setup:
>
> Multiple databases (all 8.1.7.2.x) on multiple W2K servers
>
> Background:
>
> I created a procedure to update a local table with data retrieved from
> a remote database. The (public) database link information was
> "hard-coded" in the procedure. Everything worked fine.
>
> Problem:
>
> I now need to update the local table from multiple remote databases
> (all identical column / table structure). I have all of the database
> links created, but can't figure out how to pass a variable for the
> database link. I've been trying to set up a reference cursor but can't
> figure out how to do this and be able to pass the database link to the
> procedure.
>
> I've been trying to uncover whatever I could from Oracle
> documentation, Metalink, Google, etc. but can't seem to get database
> link part figured out.
>
> Any help or pointers greatly appreciated.
>
> Thanks,
>
> Don
>

I've done EXACTLY this by creating a DB_LINK called "REMOTE". My code always references objects_at_REMOTE. At the very start of a procedure
DROP DATABASE LINK REMOTE;
CREATE DATABASE LINK REMOTE .....;
using the value desired for this invocation. The only minor "trick" is that I needed to manually create REMOTE the first time to get the PACKAGE BODY to successfully compile.

HTH & YMMV HAND! Received on Sat Feb 01 2003 - 20:42:06 CST