none
SQLServerBulkCopy - Getting error for oracle NUMBER data type without precision "Length or precision specification 0 is invalid" RRS feed

  • Question

  • Hi Experts,

    I am getting error when using SQLServerBulkCopy() java class to load values from oracle DB to SQL server. Oracle DB the NUMBER data types are defined without  precision  (default) e.g. col1 NUMBER.  the java class fails when it does not find the precision values and raise error as "Length or precision specification 0 is invalid". works fine when it defined as col1 NUMBER(n) or by using as CAST function in while selecting result set as cast(col1 as NUMBER(38,3)) col1.

    Please suggest how to get it work in SQLServerBulkCopy() without CAST() or changing the source data type.

    Sample lines of code i used

    -- select source from oracle DB

    rsSourceData = stmt.executeQuery("SELECT C1_NUMBER,C2_VARCHAR2,C3_DATE  FROM MSSQL_TYPE_TEST");

    SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection)
    bulkCopy.setDestinationTableName(destinationTable);
    bulkCopy.writeToServer(rsSourceData);

    Thanks in advance


    Wednesday, October 2, 2019 4:08 PM

All replies

  • Hi MansaMs,

    Thank you for you question.

    Maybe you can try below methods: 

    https://www.mssqltips.com/sqlservertip/5149/bulk-copy-data-from-oracle-to-sql-server/


    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.

    Thursday, October 3, 2019 2:55 AM
  • Thanks for the reply.

    I saw that the same BulkCopy method is  used for windows PowerShell.  However it loads only Number data type with non zero precision values. our source system oracle database has number data type without precision ( default null) which is causing the problem. 

    Monday, October 21, 2019 6:21 PM
  • The work around is to change the data type in Oracle, or cast it in your query. 

    Your post probably more appropriately belongs in a Java forum.

    Monday, October 21, 2019 6:34 PM
    Moderator