BINARY_FLOAT

Description

BINARY_FLOAT is a 32-bit, single-precision floating-point number data type. EachBINARY_FLOATvalue requires 4 bytes. In a BINARY_FLOATcolumn, 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_Float data type)

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

• Maximum positive finite value = 3.40282E+38F

• Minimum positive finite value = 1.17549E-38F

Sample Source Patterns

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

Binary Float in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE binary_float_data_type_table
(
col1 BINARY_FLOAT
);

INSERT INTO binary_float_data_type_table VALUES(1.17549E-38F);
INSERT INTO binary_float_data_type_table VALUES(3.40282E+38F);
INSERT INTO binary_float_data_type_table VALUES('NaN');

Snowflake

Query

OUT -> Oracle_01.sql

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

INSERT INTO binary_float_data_type_table
VALUES(1.17549E-38);

INSERT INTO binary_float_data_type_table
VALUES(3.40282E+38);

INSERT INTO binary_float_data_type_table
VALUES('NaN');

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

BINARY_FLOAT -> FLOAT

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

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM binary_float_data_type_table;

Result

col1                                   |
---------------------------------------+
                                      0|
340282001837565600000000000000000000000|
                                    NaN|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM binary_float_data_type_table;

Result

col1                                   |
---------------------------------------+
                                      0|
340282000000000000000000000000000000000|
                                    NaN|

Known Issues

1. The BINARY_FLOAT data type is not supported by Snowflake

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

Related EWIs

No related EWIs.