>
> 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.
>
You probably do not need DBD::ODBC. You can handle it yourself in a few
ways (from least attractive to most attractive):
- 1) manually write a regex to change the date coming from Sybase to your
default Oracle date format
- 2) use a module like Date::Manip to validate and get the date into the
format you want (more useful when pulling dates from text files than from
databases which should have the date in the right format in the first place)
- 3) Make your query use the TO_DATE() SQL function to have Oracle convert
the date for you.
- 4) Alter your Oracle session's NLS_DATE format to accept the date in the
same format that Sybase provides. (i.e.
$dstdbh->do("ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YY'");
The simple approach is #4, but I've used #3 above when I had lots of dates
different format dates all being read from a source with a consistent
(within the field) format and I've used #2 when reading from, say,
spreadsheets and other text formats where I could not trust the date to be
valid or in the right format and wanted to catch/fix it before
inserting/updating the database.
Regards,
Jeff
>
>
> 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.
> >
> > ????
> >
> > Thanks,
> > Wayne Rice
> > Sybase, Inc.
> > Bethesda, MD
> > Sybase V-mail: 301-896-1368
> > Sybase E-mail: [EMAIL PROTECTED]
> > NAVAIR Phone: 301-757-1657
> > NAVAIR E-mail: [EMAIL PROTECTED]
> > Cell Phone 443 540-7944
> > <<...OLE_Obj...>>
>
