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

HI,

    Why don't you use snapshots for better maintenance ?

        CIAO Sean Thornberg wrote in message <6kiu32$8c6$1_at_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 - 09:19:16 CDT