SnowConvert AI - Oracle - Data Types¶

Description¶

SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow. (Oracle Language Reference ANSI, DB2, and SQL/DS Data Types).

When creating a new table, Oracle and Snowflake handle some data types as synonyms and aliases and transform them into the default data type. As shown in the next table:

To get more information about the translation specification of the Oracle data types, go to Oracle Built-in Data Types.

Known Issues¶

No issues were found.

Related EWIs¶

EWIs related to these data types are specified in the transformation of the Oracle Built-in data types.

NUMBER to DECFLOAT Transformation¶

SnowConvert AI supports transforming Oracle NUMBER columns to Snowflake DECFLOAT data type. This is useful when you need to preserve the exact decimal precision of numeric values during migration.

When a NUMBER column is configured to be transformed to DECFLOAT:

1. The column data type in CREATE TABLE statements is transformed to DECFLOAT

2. Numeric literals in INSERT statements that target DECFLOAT columns are automatically wrapped with CAST(... AS DECFLOAT) to ensure proper data type handling

3. Column references in INSERT ... SELECT statements are also cast appropriately

CREATE TABLE products (
    product_id NUMBER(10),
    price NUMBER(15, 2)
);

INSERT INTO products VALUES (1, 99.99);

CREATE OR REPLACE TABLE products (
    product_id NUMBER(10),
    price DECFLOAT
);

INSERT INTO products VALUES (1, CAST(99.99 AS DECFLOAT));