none
SSMS Import - Oracle to SQL Server - Number data type RRS feed

  • Question

  • I am attempting to import an Oracle 11.2g table into a SQL Server 2016 database using the SSMS 2016 Import Wizard.  I am using the Oracle Provider for OLE DB (using the Oracle 11.2.0.4 client) for the Data source and the SQL Server Native Client 11.0 as the Destination.

    My problem is with the data type conversion for an Oracle NUMBER data type where the column is defined with no precision or scale - just as NUMBER.  Oracle NUMBER columns with a defined precision/scale work fine.

    When i select a table using the "Copy data from one or more tables or views" and edit the mapping, all of the NUMBER columns show as <unknown type> and i can't even manually correct the values and continue on. 

    When i use "Write a query to specify the data to transfer" and key in a "select * from tablename", then edit the mappings, it pulls over the correct NUMERIC type but puts in a precision of 38 and scale of 255.  255 is not a valid scale value.  If i follow back up and change the scale value(s) from 255 to 0 and continue, i am able to successfully import the data. 

    The final screen in the wizard identifies the provider mapping file (C:\Program Files (x86)\Microsoft SQL Server\130\DTS\MappingFiles\OracleToMSSql10.XML) - which i've looked at and attempted to modify, with no luck.

    I have a user that says he was able to do this exact import (just selecting the table from the source table listing and not having to change any mappings) prior to upgrading from SQL Server 2012 to 2016 and the Oracle client being upgraded from 11.2.0.2 to 11.2.0.4 (the Oracle database has not changed in years).  I have tried doing the same thing on a VM that only has SQL Server 2012 and the 11.2.0.2 Oracle client and i get the same results as described above.

    At the very least, does anyone have ideas how i can get the default scale value of 255 changed to 0?  Any help will be greatly appreciated!

    Wednesday, October 3, 2018 7:01 PM

All replies

  • Hi Jimz, 

    What is the exact data type in Oracle? 

    Can you provide some sample data, so that we can reproduce the issue? 

    I'm afraid there is no such option that can set scale value.

    The data types between SQL Server and Oracle do not always match exactly, sometimes manually edit mapping is needed. 

    Relative links:

    Data Type Mapping for Oracle Publishers

    Data Type Mapping in the SQL Server Import and Export Wizard



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 5, 2018 10:01 AM
  • Yang.Z,

    Thanks for the reply. 

    You can define both precision and scale when using an Oracle NUMBER data type.  As a demonstration, I created the following very basic table.  It has a number (no precision), a number (with defined precision) and a number (with precision and scale).

    create table sir.testdata
    (num_plain  number,
     num_p          number(9),
     num_p_s        number(9,2));

    insert into sir.testdata (num_plain, num_p, num_p_s) values (10,10,10.12);

    Friday, October 5, 2018 12:58 PM
  • Sorry, I inadvertently replied before completing my response.

    When I then try to import this table into SQL Server using the Import wizard, if I select it from the table list, I get the following.  Notice that it only recognizes the data type when the Oracle column was defined with some sort of size (precision,scale).

    When I try to import this table using a SELECT * from SIR.TESTDATA, I get the following.  Notice that the NUMBER column that was defined with no precision/scale, it interprets it as NUMERIC(38,255), which is an invalid definition due to scale being larger than precision.

    Again, any help will be greatly appreciated!

    Friday, October 5, 2018 1:06 PM
  • Hi Jimz_ws, 

    I have tried to import the data you provide, the same result as yours...

    I think the Import Wizard is not good at handle NUMBER data type, and I didn't find a way to fix it. 

    Actually, the Import Wizard is a simple SSIS package, you can create your own package to do this. 

    You will need Visual Studio and SSDT, to create a Integration Services Project

    Relative link:

    Importing data from Oracle database to SQL Server using SSIS

     


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 8, 2018 10:17 AM
  • Sorry, I inadvertently replied before completing my response.

    When I then try to import this table into SQL Server using the Import wizard, if I select it from the table list, I get the following.  Notice that it only recognizes the data type when the Oracle column was defined with some sort of size (precision,scale).

    When I try to import this table using a SELECT * from SIR.TESTDATA, I get the following.  Notice that the NUMBER column that was defined with no precision/scale, it interprets it as NUMERIC(38,255), which is an invalid definition due to scale being larger than precision.

    Again, any help will be greatly appreciated!

    Did you try using a full fledged SSIS package instead

    SSIS Export Import wizard is a shorthand tool with limited options so see if you can create a SSIS package with proper data conversion logic to ensure correct mapping of your data types.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, October 8, 2018 10:38 AM