Re: Import commonality between Oracle and SQL Server

I appreciate the feedback. Unfortunately you confirmed what I already knew. I wanted to make sure I had not overlooked an opportunity somewhere.

The SQL*Loader is not part of the Oracle client install so if the Oracle kernel is running on NT and the ...BIN directory has the permissions set for execution and the server is local then I may be able to use the SQL*Loader. Probably not a good idea.

I have written two polymorphic classes to handle the importing. During initialization, the app inspects the OLE provider and if it's SQLOLEDB it instantiates the class that uses the SQL server Bulk insert. If it is Oracle then it instantiates a different class that does a row by row insert.

Sybrand has a point about "porting" apps from one db to another. I can count on one hand how many successful conversions I have known about. Having said that, when the original spec includes the requirement that it works on multiple backends it is much easier to design with that goal in mind. The key to a successful roll-out is conveying expectations to the end user. When an app must be multi db compatible you must code to the lowest common denominator which usually causes all platforms to suffer.

Eddie

ps. sorry about the anonymous post - too much spam from the news crawlers

"Watchman" <dmalone_at_attglobal.net> wrote in message news:Gd6R8.104029$831.26722_at_news01.bloor.is.net.cable.rogers.com...
> I liked Pete's point about RDBMS technology bigotry. I saw Sybrand's
> remarks in the same light. It was not helpful. As a Tandem NonStop SQL
fan
> and NSK expert, I am well aware of the pitfalls of interoperability with
> other databases. There are always trade-offs.
>
> As a possible suggestion, perhaps you want to consider one of the RDBMS
> middleware products that provide "data loader" facilities to migrate data
> between databases. If you have that much data, this could prove
> cost-effective.
>
> "Pete F" <phf175SPAMBLOCK_at_hotmail.com> wrote in message
> news:3d14eb95_4_at_news.bluewin.ch...
> > As a SQL Server DBA (who knows very little about Oracle), I might add:
> >
> > There are several common mistakes made by *some* people who use Oracle:
> >
> > - They believe Oracle is The One True RDBMS
> > - They base all criticism of SQL Server on version 6.5, and are
> > breathtakingly (willfully?) ignorant of the enhancements in 7.0/2000
> > - They believe usability and self-tuning functionality are toys, and so
> > beneath the dignity of real DBAs (whatever they might be)
> >
> > Anyway, to add some hopefully more considered response to the OP:
> >
> > Bulk data loading is one of those things that is heavily dependent on
the
> > database implementation. This isn't surprising - while ANSI standards
> exist
> > for manipulating data in a database, data outside the database could, by
> > definition, be in almost any format. Sybrand is completely right that
you
> > really want to avoid trying a one size fits all solution - I have seen
> > equally appalling applications that were ported in the other direction
> > (Oracle to MSSQL) with dire results.
> >
> > Basically, you have to accept that if your requirement is to work with
> > Oracle and MSSQL, then you will inevitably require platform-specific
code
> at
> > some point. I am deeply suspicious of vendors who inform me that their
SQL
> > code (never mind bulk loading) is 'ANSI compliant' or whatever, and so
> works
> > on all platforms. This usually just means it performs equally badly on
all
> > platforms...
> >
> > Given your amount of source data, you will definitely have to use a
> > platform-specific solution to avoid the performance porblems you
mentions.
> > One possibility might be to allow the user to specify a path to BCP.EXE
> > (MSSQL) or the SQL*Loader executable, then build a dynamic command
string
> to
> > load the data. You'd have to watch out for version-specific syntax,
> though,
> > and also the myriad flags that BCP.EXE (and I'm sure SQL*Loader)
support.
> >
> > Regards,
> >
> > Pete
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:uh9o06l4c6ls01_at_corp.supernews.com...
> > >
> > > "none" <none_at_nowhere.net> wrote in message
> > > news:NW2R8.36870$OI6.4078724159_at_newssvr12.news.prodigy.com...
> > > > Is there a common function that exists in both Oracle and SQL Server
> > that
> > > > will allow bulk inserts without programmatically traversing the
entire
> > > > recordset?
> > > >
> > > > I am developing an ADO/OLEDB client server app that must be able to
> use
> > > > either an Oracle (>= 8i) or a MS SQLServer (>= 7.0) database. The
> app
> > > must
> > > > allow a user to import data from csv and fixed length files.
> SQLServer
> > > > offers the BULK INSERT functionality that simplifies operation and
> > > enhances
> > > > performance. Oracle has the SQLLoader external program for
importing
> > > files.
> > > >
> > > > The database will store approximately 5 million items and will be
> loaded
> > > in
> > > > batches of 50K to 100K rows. I have tried to traverse the input
file
> > > record
> > > > by record inserting rows but the process takes hours. I have
> > considered
> > > > writing db specific code but would like to avoid multiple codesets
if
> > > > possible.
> > > >
> > > > Thanks in advance,
> > > > Eddie
> > > >
> > > >
> > > >
> > > There are several common mistakes made by people brainwashed by
> Sqlserver.
> > > - They never read manuals
> > > - They always crosspost
> > > - They think Oracle is Sqlserver sold by a different vendor.
> > >
> > > As someone who is always confronted with 3rd party apps, that
originated
> > > from Sqlserver and were simply 'ported' to Oracle, I can assure you
this
> > is
> > > the most secure way to disaster performance. Evidently the application
> > > vendor and their developers in that situation invariably blame Oracle
> and
> > > the Oracle DBA for not configuring Oracle correctly.
> > > Read the first chapters of the book by Thomas Kyte and return from
your
> > > route to hell.
> > >
> > > Regards
> > >
> > >
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
> > >
> > > to reply remove '-verwijderdit' from my e-mail address
> > >
> > >
> > >
> >
> >
>
>
Received on Sun Jun 23 2002 - 15:50:51 CDT