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 datatypes 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 MSCEWI1066 issue.

Sample Source Patterns

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

Number data types in Create Table

Oracle

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

CREATE OR REPLACE TABLE number_data_type_table(
col1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
col2 NUMBER (1) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
col3 NUMBER (10, 5) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
col4 NUMBER (5) /*** MSC-WARNING - MSCEWI3092 - NUMBER DATATYPE NEGATIVE SCALE WAS REMOVED FROM OUTPUT ***/ /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
col5 NUMBER (5,5) /*** MSC-WARNING - MSCEWI3093 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/);

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

SELECT col1 FROM number_data_type_table;

Snowflake

SELECT col1 FROM number_data_type_table;

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

SELECT col2 FROM number_data_type_table;

Snowflake

SELECT col2 FROM number_data_type_table;

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

SELECT col3 FROM number_data_type_table;

Snowflake

SELECT col3 FROM number_data_type_table;

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

SELECT col4 FROM number_data_type_table;

Snowflake

SELECT col4 FROM number_data_type_table;

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

SELECT col5 FROM number_data_type_table;

Snowflake

SELECT col5 FROM number_data_type_table;

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 issue please go to the MSCEWI1066 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 MSCEWI3092 documentation.

Related EWIs

  1. MSCEWI1066: Number type column may not behave similarly in Snowflake

  2. MSCEWI3092: Number datatype negative scale was removed from output

  3. MSCEWI3093: Number datatype smaller precision was increased to match scale

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:

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);
}
}';
PreviousNumeric Data TypesNextFLOAT Data Type

Last updated