Re: Import commonality between Oracle and SQL Server

Pete F wrote:

> 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
> >
> >
> >

Your attempt to start a flame war (overt or inadvertent) appear to have failed and for that I am glad. We can live quite nicely here without a hand grenade being tossed in.

As someone that has been involved in many projects moving applications from SQL Server to Oracle, and visa versa, one thing has become very apparent. And that is that Oracle people tend to assume they need to learn SQL Server while SQL Server people feel that Oracle should behave exactly like a Microsoft product. So Oracle products migrated to SQL Server generally perform quite well and SQL Server products migrated to Oracle often are disasters with the finger of blame being pointed to the software rather than the developers.

Daniel Morgan Received on Mon Jun 24 2002 - 10:25:33 CDT