I think the question would be, "how often are you going to do this"? Will it be just once? Once a month? Once a Day? How much performance will you get from sqlldr? Everything I have read says sqlldr is faster than using Perl. I think it depends on the environment. Especially how Oracle is setup. I personally have seen better performance using Perl rather than sqlldr (believe me, it surprised me to see it). If this is something that will be done often and the data transfer needs to be as near to realtime as can be then some inhouse benchmarking is warranted.
Chris Shelton wrote: > > Wayne, > > We went throw a sybase->oracle data conversion a couple of years ago. An > in-house developed perl script went through the following steps to > accomplish the data migration: > > - check data types on sybase side > - if table didn't exist on oracle, > - create compatible structure in oracle > - if table did exist in oracle > - compare datatypes on both source & destination to insure compatibility > - build sql statment to select data from sybase table > (datetime, text, image columns required special handling -- precision of > less than 1s was lost in migration to oracle; text/image was handled by > a second script, after this one was completed.) > - build oracle sqlldr control file, with input coming from named pipe > - start sqlldr process in background, then start sybase select. > - wait for both sides to finish, printing process indication every 1000 > rows or so. > - count rows on each side. > > This process was incredibly fast-- the data was moved into oracle as fast > as it could be selected from sybase. The use of a named pipe let us not > need to ever store the data on disk during the conversion. I believe that > we tried using standard insert statements instead of sqlldr, but the > performance advantages of sqlldr were worth the additional coding required > to make it work. > > chris > > On Mon, 8 Apr 2002, Rice, Wayne R (Sybase) wrote: > > > Thanks! Question: does the Oracle side have to use DBD::ODBC (with Oracle > > ODBC) to work due to the date verses datetime datatype issues? I would > > prefer to just use DBD::ORACLE in hopes of it being faster. > > > > -----Original Message----- > > From: Ian Harisay [mailto:[EMAIL PROTECTED]] > > Sent: Friday, April 05, 2002 5:42 PM > > To: Rice, Wayne R (Sybase); [EMAIL PROTECTED] > > Subject: Re: HOW TO: insert into OracleTableX select * from SybaseTableY > > > > > > If the tables were exactly the same this will work You may need to tweak > > for > > your purpose. > > > > $sdbh ## is your sybase database handle. > > $odbh ## is your oracle database handle. > > > > $ssth = $sdbh->prepare("select * from table_name"); > > $ssth->execute(); > > > > $osth = $odbh->prepare("insert into table_name (field1, field2) values (?, > > ?)"); > > > > while ( my $rec = $ssth->fetchrow_arrayref() ){ > > $osth->execute( @{ $rec } ); > > } > > $ssth->finish; > > $osth->finish; > > > > $sdbh->disconnect; > > $odbh->disconnect; > > > > "Rice, Wayne R (Sybase)" wrote: > > > > > > I wish to do a bulk insert into select from of all rows. > > > I have my connection to Oracle (source) and Sybase (destination) set up > > and > > > working. > > > > > > What is the BEST and FASTEST way to do something like this: > > > > > > insert into OraX select * from SybY Note: the col names are the same > > > and datatypes as close a possible. > > >
