Oracle - Data Types¶

This section shows equivalents between data types in Oracle and Snowflake, as well as some notes on arithmetic differences.

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.

ANSI 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.

Data Type Customization¶

Data Type Customization is enabled to specify rules for data type transformation based on data type origin and column name. This feature allows you to personalize data type conversions and set precision values more accurately during migration.

For complete documentation on configuring data type customization, including JSON structure, configuration options, and priority rules, see Data type mappings.

Data type mappings¶

Default mappings for data type conversions are defined. However, you can point to a JSON file to customize specific data type mappings.

Customize data types: You can upload a JSON file to define specific data type transformation rules. This feature allows you to customize how data types are converted during migration.

Supported transformations include:

• NUMBER to custom NUMBER with specific precision and scale
• NUMBER to DECFLOAT for preserving exact decimal precision

When you upload a data type customization file:

• Your transformation rules are applied during conversion
• Numeric literals in INSERT statements targeting customized columns are automatically cast to the appropriate type
• A TypeMappings Report is generated showing all data type transformations applied

JSON Structure:

The JSON file supports three ways to specify data type changes:

Priority order: When multiple rules apply to the same column, the following priorities are used from highest to lowest:

1. specificTableTypeChanges (most specific)
2. projectTypeChanges.columns (name pattern)
3. projectTypeChanges.types (global type mapping)

Example JSON configuration:

{
  "projectTypeChanges": {
    "types": {
      "NUMBER": "DECFLOAT",
      "NUMBER(10, 0)": "NUMBER(18, 0)"
    },
    "columns": [
      {
        "nameExpression": "PRICE",
        "targetType": "DECFLOAT"
      },
      {
        "nameExpression": ".*_AMOUNT$",
        "targetType": "NUMBER(18, 2)"
      }
    ]
  },
  "specificTableTypeChanges": {
    "tables": [
      {
        "tableName": "EMPLOYEES",
        "columns": [
          {
            "columnName": "SALARY",
            "targetType": "NUMBER(15, 2)"
          }
        ]
      }
    ]
  }
}

Download template: Copy and save the JSON structure above as your starting point.

Example transformation:

Given the following Oracle input code:

Oracle¶

CREATE TABLE employees (
    employee_ID NUMBER,
    manager_YEAR NUMBER(10, 0),
    manager_MONTH NUMBER(10, 0),
    salary NUMBER(12, 2)
);

And a JSON customization file with:

• "NUMBER": "NUMBER(11, 2)" in projectTypeChanges.types
• "NUMBER(10, 0)": "NUMBER(18, 0)" in projectTypeChanges.types
• "MONTH" pattern targeting NUMBER(2,0) in projectTypeChanges.columns
• SALARY column targeting NUMBER(15, 2) in specificTableTypeChanges for EMPLOYEES table

The output will be:

Snowflake¶

CREATE OR REPLACE TABLE employees (
    employee_ID NUMBER(11, 2),
    manager_YEAR NUMBER(18, 0),
    manager_MONTH NUMBER(2, 0),
    salary NUMBER(15, 2)
);

NUMBER to DECFLOAT Transformation¶

Transforming Oracle NUMBER columns to Snowflake DECFLOAT data type is supported. 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

Example¶

Oracle¶

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

INSERT INTO products VALUES (1, 99.99);

Snowflake (with DECFLOAT customization for price column)¶

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

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