Re: Importing SQLServer Database into Oracle DB

We migrated a bunch of Sybase databases to Oracle (Sybase and MS are close cousins - they used to be the same product running on different platforms a few versions ago).

For the structure, we used Erwin to reverse engineer the database from SQL Server then forward engineer it as Oracle. This was worthwhile because we could clean up naming conventions, datatype lengths, etc. Also gives you a chance to insert meaningful storage parameters.

For the data, we used BCP to get the data out, then used Oracle's SQL Loader to import the data. This was a pain on the Oracle side because you need to create a control file for each table. We wound up writing scripts to automate control file creation.

We had a number of issues with the data itself: 1. SQL Server stores datetime fields down to 1/1000 of a second, vs whole seconds in Oracle. This caused extra problems if the datetime field was part of the primary key (duplicate key errors). 2. No equivalent of an identity column in Oracle, though you can fake it with a sequence combined with an insert trigger. 3. Typically SQL Server databases use triggers for referential integrity instead of constraints (usually for performance reasons). Most Oracle folks use constraints. Since triggers don't check existing data you may find foreign key violations when you enable constraints. 4. We wound up using hex field delimiters since our users have put every character imaginable into their data.
5. SQL*Loader expects a newline as the record delimiter - if your users have newlines embedded in their data anywhere, SQL*Loader will choke. Oracle's website has a white paper on how to get around this with a couple of C programs.
6. List of reserved words is different

"Chris France" <schris_at_free.fr> wrote in message news:3e1c6fa3$0$11413$626a54ce_at_news.free.fr...
> Hi all,
> I am trying to perform exportation of my SQL Server 2000 database to
Oracle
> 8 database.
> The DTS from Mircrosoft seems not to allow me to transfer data but only db
> structure ...
> I dont know Oracle enough yet to know if there is a tool into Oracle's
> package (never heard of it).
> Does any one knows a tool to transfert a DB from SQLServer to Oracle
> including data ?
> Thanks
>
>
Received on Wed Jan 08 2003 - 13:23:12 CST