ANSI Data Types

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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:

ANSI	ORACLE	SNOWFLAKE
CHARACTER (n)	CHAR (n)	VARCHAR
CHAR (n)	CHAR (n)	VARCHAR
CHARACTER VARYING (n)	VARCHAR2 (n)	VARCHAR
CHAR VARYING (n)	VARCHAR2 (n)	VARCHAR
NATIONAL CHARACTER (n)	NCHAR (n)	VARCHAR*
NATIONAL CHAR (n)	NCHAR (n)	VARCHAR*
NCHAR (n)	NCHAR (n)	VARCHAR
NATIONAL CHARACTER VARYING (n)	NVARCHAR2 (n)	VARCHAR*
NATIONAL CHAR VARYING (n)	NVARCHAR2 (n)	VARCHAR*
NCHAR VARYING (n)	NVARCHAR2 (n)	NUMBER (p, s)
NUMERIC [(p, s)]	NUMBER (p, s)	NUMBER (p, s)
DECIMAL [(p, s)]	NUMBER (p, s)	NUMBER (38)
INTEGER	NUMBER (38)	NUMBER (38)
INT	NUMBER (38)	NUMBER (38)
SMALLINT	NUMBER (38)	NUMBER (38)
FLOAT	FLOAT (126)	DOUBLE
DOUBLE PRECISION	FLOAT (126)	DOUBLE
REAL	FLOAT (63)	DOUBLE

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

VARCHAR*: Almost all the ANSI datatypes compile in Snowflake, but those marked with an asterisk, are manually converted to VARCHAR.

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.