Best way to transfer data using PL/SQL between 2 DB's?

From: Sean Thornberg <seant1_at_gte.net>
Date: Thu, 28 May 1998 00:52:24 -0500
Message-ID: <6kiu32$8c6$1@gte2.gte.net>

I am having to transfer large amounts of data from one Oracle database to another with some major data scrubbing on the primary key. The following is how I have it working right now and I am wondering if I can change it to improve speed.

first I run a SQL statement:

delete from DestTable
where PK1 = pk1 -- pk1 is a variable

then I run the procedure:

Procedure DL(PK1 varchar2) is
OldPK2 varchar2;
NewPK2 varchar2;
cursor c1 is

   select

      pk1,
      pk2,
      data1,
      data2

   from SourceTable_at_SourceDB
   where

      pk1 = PK1; -- getting PK1 from passed parameter begin
for rec in c1 loop

   begin
   OldPK2 = rec.pk2;

• do some major data scrubbing to get NewPK2 insert into DestTable
  (
  PK1, PK2, data1, data2 ) values
  (
  PK1, NewPK2, rec.data1, rec.data2 ) exception when DUP_VAL_ON_INDEX then null; end end DL;

Thanks,

   Sean Received on Thu May 28 1998 - 00:52:24 CDT