Oracle to MS SQL - an error "Arithmetic overflow error converting nvarchar to data type numeric."

Problem Description

We have recently come across an "arithmetic overflow error" when replicating from Oracle database to Microsoft's SQL database. We have been testing random large numbers that are stored in the oracle's NUMBER datatype column (created with default specification without specifying scale and/or precision values).

Solution

The "arithmetic overflow error" happens due to scale and precision limitation of NUMERIC and DECIMAL datatypes in MS SQL 2012 and below.

Applies To: SQL Server 2014, SQL Server 2016 Preview Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2. In SQL Server, the default maximum precision of numeric and decimal data types is 38. In earlier versions of SQL Server, the default maximum is 28.

Example

Lets see what my oracle and sql table DDLs initially looked like:

Oracle

CREATE TABLE "DEMO"."MYK\_TEST"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(20),
"NUM" NUMBER,
CONSTRAINT "MYK\_TEST\_PK" PRIMARY KEY ("ID"));

SQL

CREATE TABLE [demo].[MYK\_TEST](
[ID] [numeric] NOT NULL,
[NAME] [nvarchar](20) NULL,
[NUM] [numeric] NULL
) ON [PRIMARY]

...and here is a simple insert / update loop to populate the tables. This is where I got the "arithmetic overflow error":

> BEGIN
FOR i IN REVERSE 1..3000000 LOOP -- i starts at 10000, ends at 1
DBMS\_OUTPUT.PUT\_LINE(i); -- statements here execute xx times
> insert into demo.myk\_test values (myk\_test\_seq.nextval,'MARTIN',(myk\_test\_seq.nextval+1000));
commit;
> UPDATE demo.myk\_test
SET
"NAME" = DBMS\_RANDOM.STRING('X',10),
"NUM" = DBMS\_RANDOM.VALUE(1,9);
> commit;
>
END LOOP;
END;

Here is the error I have received:

Here are some possible workarounds:

Create a NUMBER (datatype) column with precision and scale values that are not larger than the limitations of NUMERIC and DECIMALdatatypes in versions of MS SQL 2012 and below.
Create your source NUMBER datatype column by specifying 28 or less as a scale/precision max value.
Upgrade (and test, test, test!!!) your target database to MS SQL 2014 and above.
Trunc and/or round the number in your source insert/update SQL (see example below).

Martin Knazovicky July 25, 2015 19:40