Database Ingestion and Replication

Back Next

Oracle Source and Snowflake Target

The following table identifies the recommended data-type mappings for

configurations with an Oracle source and a Snowflake target:

Oracle Source Data Type	Snowflake Target Data Type
binary_double	double
binary_float	double
blob	binary
char(s byte), 1 <= size <= 2000	varchar(4)
char(s char), 1 <= size <= 2000	varchar(4)
clob	varchar
date	timestamp_ntz(0)
float(`precision`), 1 <= p <= 126	varchar(255)
integer	varchar(255)
long raw	binary
long(2147483648 bytes)	varchar(65535)
nchar(s char), 1 <= size <= 2000	varchar(4)
nclob	varchar
number	char(255)
number(p,s), 1<=p<=38, 0<=s<=37	number
number(p,s), 1<=p<=38, -37<=s<=-1	number, when total length is less than or equal to 38 varchar, when total length is greater than 38
nvarchar2(s char), 1 <= size <= 4000	varchar(4)
raw(`size`), 1 <= size <= 2000	binary(`size`), 1 <= size <= 2000
rowid	varchar(18)
timestamp(`precision`) with local time zone, 0 <= p <= 9	timestampltz (`precision`), 0 <= p <= 9
timestamp(`precision`) with time zone, 0 <= p <= 9	timestamp_tz(`precision`), 0 <= p <= 9
timestamp(`precision`), 1 <= p <= 9	timestamp_ntz(`precision`), 1 <= p <= 9
varchar2(s byte), 1 <= size <= 4000	varchar(`size`), 4 <= size <= 4000
varchar2(s char), 1 <= size <= 4000	varchar(`size`), 4 <= size <= 16000
xml	varchar

LOB limitations

Database ingestion and replication

initial load, incremental load, and combined load jobs can replicate data from Oracle BLOB, CLOB, NCLOB, LONG, LONG RAW, and XML columns to Snowflake targets. LOB column data might be truncated on the target. For initial load jobs with a Snowflake target, BLOB and LONG RAW data is truncated to 8388608 bytes, and CLOB, LONG, NCLOB, and XML data is truncated to 16777216 bytes. For more information, see the "Include LOBs" description in Configuring the source.

Unsupported source data types

Database Ingestion and Replication

does not support the following Oracle source data types with any load type:

"ANY" types such as ANYTYPE, ANYDATA, ANYDATASET

Extended types

INTERVAL

JSON

BFILE type for storing LOB data externally

UROWID

Spatial types such as SDO_GEOMETRY

User-defined types such as OBJECT, REF, VARRAY, nested table types

Source columns that have unsupported data types are excluded from the target definition.