NUMBER Data Type

Description

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to but not including 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. (Oracle Language Reference Number Data Type).

The NUMBER data type can be specified using the following form NUMBER(p, s) (both parameters are optional) where:

• p is the precision or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. The precision can range from 0 to 38.

• s is the scale or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.

On Oracle, not specifying precision (using NUMBER or NUMBER(*)) causes the column to be created as an "undefined precision". This means that Oracle will store values dynamically, allowing to store any number within that column. Snowflake does not support this functionality; for this reason, they will be changed to NUMBER(38, 18), allowing to store the widest variety of numbers.

Notes on arithmetic operations

Please be aware that every operation performed on numerical data types is internally stored as a Number. Furthermore, depending on the operation performed it is possible to incur an error related to how intermediate values are stored within Snowflake, for more information please check this post on Snowflake's post on intermediate numbers in Snowflake or check the functional equivalence message SSC-FDM-0006.

Sample Source Patterns

Please, consider the following table and its inserts for the examples below:

Number data types in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE number_data_type_table
(
col1 NUMBER,
col2 NUMBER(1),
col3 NUMBER(10, 5),
col4 NUMBER(5, -2),
col5 NUMBER(4, 5)
);

INSERT INTO number_data_type_table(COL1) VALUES(100);
INSERT INTO number_data_type_table(COL2) VALUES(1.99999);
INSERT INTO number_data_type_table(COL3) VALUES(12345.12345);
INSERT INTO number_data_type_table(COL4) VALUES(16430.55555);
INSERT INTO number_data_type_table (COL4) VALUES(17550.55555);
INSERT INTO number_data_type_table(COL5) VALUES(0.00009);
INSERT INTO number_data_type_table(COL5) VALUES(0.000021);
INSERT INTO number_data_type_table(COL5) VALUES(0.012678912);

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE number_data_type_table
(
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col2 NUMBER(1) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col3 NUMBER(10, 5) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col4 NUMBER(5) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0092 - NUMBER DATATYPE NEGATIVE SCALE WAS REMOVED FROM OUTPUT ***/!!! /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col5 NUMBER(5, 5) /*** SSC-FDM-OR0010 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO number_data_type_table(COL1) VALUES(100);

INSERT INTO number_data_type_table(COL2) VALUES(1.99999);

INSERT INTO number_data_type_table(COL3) VALUES(12345.12345);

INSERT INTO number_data_type_table(COL4) VALUES(16430.55555);

INSERT INTO number_data_type_table(COL4) VALUES(17550.55555);

INSERT INTO number_data_type_table(COL5) VALUES(0.00009);

INSERT INTO number_data_type_table(COL5) VALUES(0.000021);

INSERT INTO number_data_type_table(COL5) VALUES(0.012678912);

NUMBER ( default case )

When the precision and the scale are not specified, the default values are the maximum availableNUMBER(38, 127) . The current transformation for the default case is NUMBER(38,19).

In Oracle, not defining Precision nor scale defaults to an "Undefined Precision and Scale". It behaves by storing the input "as received", which means it can both deal with Integer and Floating point numbers. We use 38, 18 to try to cover both of them, by using 20 for integers, and leaving 18 for floating-point digits.

Oracle

Query

IN -> Oracle_02.sql

SELECT col1 FROM number_data_type_table;

Result

col1|
----+
 100|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT col1 FROM
number_data_type_table;

Result

col1                   |
-----------------------+
100.0000000000000000000|

NUMBER ( p )

In this case, the precision will specify the number of digits that the number could have at the left of the decimal point.

Oracle

Query

IN -> Oracle_03.sql

SELECT col2 FROM number_data_type_table;

Result

col2|
----+
   2|

Snowflake

Query

OUT -> Oracle_03.sql

SELECT col2 FROM
number_data_type_table;

Result

col2|
----+
   2|

NUMBER ( p, s ) p > s

In the case where the s is lower than the p, the precision will specify the number of digits that the number could have. The scale will specify the number of significant digits to the right of the decimal point, so the number of digits at the left of the decimal point will depend on the scale specified.

Oracle

Query

IN -> Oracle_04.sql

SELECT col3 FROM number_data_type_table;

Result

col3       |
-----------+
12345.12345|

Snowflake

Query

OUT -> Oracle_04.sql

SELECT col3 FROM
number_data_type_table;

Result

col3       |
-----------+
12345.12345|

NUMBER ( p, -s )

A negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For the negative scale, the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. The current transformation is to remove the negative scale.

Oracle

Query

IN -> Oracle_05.sql

SELECT col4 FROM number_data_type_table;

Result

col4 |
-----+
16400|
17600|

Snowflake

Query

OUT -> Oracle_05.sql

SELECT col4 FROM
number_data_type_table;

Result

col4 |
-----+
16431|
17551|

NUMBER ( p, s ) s > p

When the scale is greater than the precision, consider the following aspects:

• The number to insert could not have significant digits to the left of the decimal point. Only zero is available.

• The first digit to the right of the decimal point must be zero.

• The precision specifies the maximum number of significant digits to the right of the decimal point.

Oracle

Query

IN -> Oracle_06.sql

SELECT col5 FROM number_data_type_table;

Result

col5   |
-------+
0.00009|
0.00002|
0.01268|

Snowflake

Query

OUT -> Oracle_06.sql

SELECT col5 FROM
number_data_type_table;

Result

col5   |
-------+
0.00009|
0.00002|
0.01268|

Known Issues

1. Scale value exceeds the maximum allowed by Snowflake

When specifying a scale greater than the maximum allowed in Snowflake (37) it is being changed to 18. To get more information about this please go to the SSC-FDM-0006 documentation.

2. Negative scale

Snowflake does not allow negative scale, so it is being removed. This could cause functional inequivalence. To get more information about this issue please go to the SSC-EWI-0R0092 documentation.

Recommendations

1. UDF for NUMBER datatype Operations

It is possible to migrate these operations manually by using the next UDF when performing arithmetic operations to avoid incurring the issues noted:

UDF

CREATE OR REPLACE FUNCTION fixed_divide(a NUMBER(38,19), b NUMBER(38,19))
RETURNS NUMBER(38,19)
LANGUAGE JAVA
CALLED ON NULL INPUT
HANDLER='TestFunc.divide'
AS
'
import java.math.BigDecimal;
import java.math.RoundingMode;
class TestFunc {
public static BigDecimal divide(BigDecimal a, BigDecimal b) {
return a.divide(b,RoundingMode.HALF_UP);
}
}';

Related EWIs

1. SSC-EWI-OR0092 Number datatype negative scale was removed from output.
2. SSC-FDM-0006: Number type column may not behave similarly in Snowflake
3. SSC-FDM-OR0010 Number datatype smaller precision was increased to match scale