question

basit 1 avatar image
basit 1 asked

Issue facing while Data Converstion from Oracle to SQL ?

Hi All, I have to migrate one Oracle Schema to SQL Server 2008 . In this Schema there are around 100 Table . I am Using SSMA tool for Oracle to SQL Migration. I am facing the Issue while covnerting NUMBER Datatype in oracle . In the tool there is one option of type mapping whwere converstion is showing NUMBER to Float. i change this setting to Numeric in the tool in default setting. But when i convert the Schema it convert into float datatype. this change happen only when i manually change in every table. Is there any way where i can make the change one time and it work. For manually changing each and every table is Quit tough.. Hope you understand my Question. Thanks to Every one again for helping me whenever i face some issue. Basit Khan
sql-server-2008oraclemigration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
Instead of changing it in the default project settings, just make the modification in the project settings, that is **Tools** -> **Project Settings** -> **Type Mapping** -> **Select a Type Mapping** Option ***changed*** to **Columns type mapping** -> **number** and also do not forget the number with precision and scale (number [\*..\*] and number [\*..\*][\*..\*]). One thing which is worth mentioning is that when you change the mapping from number to numeric, choose the scale and precision wisely and beware that sometimes it might give you a small problem if the precisions of the number columns referencing some other columns in Oracle tables do not match each other. For example, suppose you have a parent table in Oracle which has a column of NUMBER(15) data type and this column is referenced in the other (child) table, but sadly the child table's column is of NUMBER type not NUMBER(15) because whoever created the child table did not bother to match the type exactly. So, if you map the NUMBER to some default precision and zero scale but borrow the actual precision when mapping number [\*..\*], you will run into trouble. In Oracle, you can still create a foreign key relationship even though the sizes don't match, but you will get an error when the SSMA tool will try to create a foreign key in SQL Server table (because it does not allow type mismatch between PK and FK). Hope this helps, Oleg
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg +1. Great advice.
1 Like 1 ·
Thanks you very much , it help me alot..
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.