BINARY_DOUBLE

Description

BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes. In a BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number). (Oracle Language Reference Binary_Double data type)

It is possible to specify floating-point numbers within the next limits:

• Maximum positive finite value = 1.79769313486231E+308

• Minimum positive finite value = 2.22507485850720E-308

Sample Source Patterns

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

Binary Double in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE binary_double_data_type_table
(
COL1 BINARY_DOUBLE
);

INSERT INTO binary_double_data_type_table VALUES(2.22507485850720E-308D);
INSERT INTO binary_double_data_type_table VALUES(1.79769313486231E+308D);
INSERT INTO binary_double_data_type_table VALUES('NaN');

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE binary_double_data_type_table
(
COL1 FLOAT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO binary_double_data_type_table
VALUES(2.22507485850720E-308);

INSERT INTO binary_double_data_type_table
VALUES(1.79769313486231E+308);

INSERT INTO binary_double_data_type_table
VALUES('NaN');

'NaN' means Not a Number, this value is allowed by theBINARY_DOUBLE data type in Oracle and by theFLOATdata type in Snowflake.

BINARY_DOUBLE -> FLOAT

Since theBINARY_DOUBLEdata type is not supported by Snowflake it is being converted to FLOAT.

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM binary_double_data_type_table;

Result

col1                                                                                                                                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
                                                                                                                                                                                                                                                              0|
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
                                                                                                                                                                                                                                                            NaN|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
binary_double_data_type_table;

Result

col1                                                                                                                                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
                                                                                                                                                                                                                                                              0|
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
                                                                                                                                                                                                                                                            NaN|

Known Issues

1. The BINARY_DOUBLE data type is not supported by Snowflake

The BINARY_DOUBLE data type is converted to FLOAT since it is not supported by Snowflake.