x

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

asked Jun 06, 2012 at 01:34 PM in Default

basit 1 gravatar image

basit 1
499 51 61 84

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

answered Jun 06, 2012 at 02:52 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Thanks you very much , it help me alot..
Jun 06, 2012 at 03:17 PM basit 1
@Oleg +1. Great advice.
Jun 07, 2012 at 04:32 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x378
x60

asked: Jun 06, 2012 at 01:34 PM

Seen: 1266 times

Last Updated: Jun 07, 2012 at 04:32 AM