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

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

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

IN -> Oracle_02.sql
SELECT col1 FROM number_data_type_table;

Snowflake

OUT -> Oracle_02.sql
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

IN -> Oracle_03.sql
SELECT col2 FROM number_data_type_table;

Snowflake

OUT -> Oracle_03.sql
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

IN -> Oracle_04.sql
SELECT col3 FROM number_data_type_table;

Snowflake

OUT -> Oracle_04.sql
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

IN -> Oracle_05.sql
SELECT col4 FROM number_data_type_table;