migration from sqlserver to oracle

[anandkl]

Hi friends,
I have just migrated tables from ms sql server into oracle using MS DTS.
I log into oracle and see all the tables but when i select from a migrated table it says table or view does not exists.

After migration so i need to do any thing else.so that oracle recognises the MS SQL table format.please help

cheese
anandkl

[TimHall]

Hi.

I've seen something similar before. When you migrate tables with mixed case table and column names the DTS preserves the case by using "" around the definition. Since oracle tools are generally case insensitive when you type:

SELECT * FROM my_table;

it won't find a match. What you need to do is type:

SELECT * FROM "My_Table";
or
SELECT "My_Column" FROM "My_Table";

To solve this do the following:

RENAME "My_Table" To my_table;

The table reference should now be fixed. If you have columns with this problem you will have to recreate the table.

Where I can, I force developers to always use lowercase names for tables and columns. DTS seems to deal with these OK.

Alternatively use DTS to produce the schema creation script. Edit it manually and use this to create your schema. Once done you can use DTS to transfer the data. Not tried this second approach recently so don't quote me

Hope this helps.

[Edited by TimHall on 01-23-2002 at 07:48 AM]

[anandkl]

thanks timhall,
Thats was great.....thanks once again

cheese
anandkl

[zztop]

HI
Is there any easier way to migrate from MS SQL to oracle coz i have the stored procedures which use MS transaction server?