Re: Variable database link in cursor

From: John Russell <netnews4_at_johnrussell.mailshell.com>
Date: Sun, 02 Feb 2003 20:27:05 GMT
Message-ID: <25vq3v437ginnstn31brn1v41vet75aafr@4ax.com>

On Sun, 02 Feb 2003 02:27:36 GMT, Don A. <dba_at_direct.ca> wrote:

>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.

Not sure how much data is involved or what form the updates take. I would start with

• Create empty table that matches all the others. CREATE GLOBAL TEMPORARY TABLE xyz AS SELECT * FROM template_table WHERE 1 = 0;

Then, in PL/SQL, something like

EXECUTE IMMEDIATE 'INSERT INTO xyz SELECT * FROM ' || remote_table || ' WHERE ...'; and put values like table_name_at_link_name into the remote_table variable.

Depending on what you want to do and how much data there is, you might be able to avoid the temporary table. For example, you could declare a collection of template_table%ROWTYPE and use EXECUTE IMMEDIATE ... BULK COLLECT INTO ... to bring all the data into an in-memory PL/SQL data structure.

John Received on Sun Feb 02 2003 - 14:27:05 CST