How to tranfer tables from SQLServer to Oracle

sankar18 · Post by **sankar18** » Tue Dec 17, 2002 12:16 am

Hi All,

Assume the case
---------------

If a SQLServer Database is having 1000 tables and 100 stored procedures, how to transfer all the 1000 tables and 100 stored procedures to Oracle Database. Is there any option in DataStage which is similar to Data Transformation Service in SQLServer.

Thanks in advance,
T Sankar

chulett · Post by **chulett** » Tue Dec 17, 2002 9:46 am

Even though it is more powerful than a speeding locomotive, this isn't a job for DataStage... or any other ETL tool, I would imagine. This just seems like 'normal' DBA work to me.

Now, perhaps once the tables and procedures have been migrated, you could look at using DataStage to transfer the *data*, but you'd end up writing 1000 jobs to do it. Must be a better way.

-craig

WoMaWil · Post by **WoMaWil** » Tue Dec 17, 2002 3:50 pm

Sakar,

craig is right. DataStage is a Tool to transfer Data on a permanent and frequent basis including a certain transformation. In doing this DataStage is a very good an flexible Tool. For to transfer Tables as they are and only once you should simply copy them. Your problem will be the Stored procedures and any kind of Triggers if you have them. It may be that you have to rewrite them all.

But you cannot blame DataStage for this. It is the same as to use a screwdrive for to dig the garden.

Wolfgang

ray.wurlod · Post by **ray.wurlod** » Tue Dec 17, 2002 5:03 pm

AND (I am assuming from the requirement to move stored procedures) the original poster wants to move table definitions (metadata) as well.
To some extent this can be handled via MetaStage, however there is no guarantee that stored procedures from SQL Server will work in Oracle. In fact I would go so far as almost to guarantee that none will; they use an almost totally different syntax.

vmcburney · Post by **vmcburney** » Tue Dec 17, 2002 11:19 pm

You need the Oracle Migration Workbench, designed to simplify the process of migrating 3rd party databases to Oracle. Have a look at this link:
http://otn.oracle.com/tech/migration/wo ... ntent.html

Note that it can migrate stored procedures and triggers. I've been on a couple projects where this would have saved a lot of time!

Microsoft has information on migration from Oracle to SQL Server with good descriptions of the differences between the two:
http://www.microsoft.com/sql/techinfo/d ... Oracle.asp

regards
Vincent

amaruvi · Post by **amaruvi** » Tue Jan 07, 2003 3:12 am

I too think that Oracle Migration Workbench is the best tool available for this specific purpose. Also this is available as free down load from Oracle.

This tool proved to be a boon when migrating an application and DB
from MS Access to Oracle. With very little effort we were able
to migrate the DB into Oracle and the Access Application was also
working with the new Oracle DB.

Amaruvi

raju_chvr · Post by **raju_chvr** » Fri Dec 12, 2003 12:28 pm

I think you can also do this with MetaRecon. To the best of my knowledge I do remember that MetaRecon can analyse any DS connected throught ODBC. So in your case if you have MetaRecon, just take SQL server as your input and you can select Oracle as an target database. MetaRecon also created basic jobs for you to transform data from the source to target databases.

Just an idea ...

DSXchange

How to tranfer tables from SQLServer to Oracle

How to tranfer tables from SQLServer to Oracle

Re: How to tranfer tables from SQLServer to Oracle