Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!wn12feed!worldnet.att.net!204.127.198.203!attbi_feed3!attbi.com!sccrnsc02.POSTED!not-for-mail From: John Russell Newsgroups: comp.databases.oracle.server Subject: Re: Variable database link in cursor Message-ID: <25vq3v437ginnstn31brn1v41vet75aafr@4ax.com> References: <8d0p3v4los3vl0dlrm70cubjvrlbtt58e2@4ax.com> X-Newsreader: Forte Agent 1.92/32.572 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 30 NNTP-Posting-Host: 12.233.47.150 X-Complaints-To: abuse@attbi.com X-Trace: sccrnsc02 1044217625 12.233.47.150 (Sun, 02 Feb 2003 20:27:05 GMT) NNTP-Posting-Date: Sun, 02 Feb 2003 20:27:05 GMT Organization: AT&T Broadband Date: Sun, 02 Feb 2003 20:27:05 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:174522 X-Received-Date: Sun, 02 Feb 2003 13:26:25 MST (news.easynews.com) On Sun, 02 Feb 2003 02:27:36 GMT, Don A. 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@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