Supported Oracle data types

Last updated: Jul 30, 2024

When you use Oracle as a source connection, you can replicate only some data types.

When you use the Oracle connection with the Copy goal, Data Replication will attempt to create or replicate to a column with a similar column name and compatible data type. If the data type of the target column is not compatible, it will convert the target data type to a string-based data type (for example, varchar).

The following table shows the Oracle data types that you can replicate. Unsupported data types are not replicated to the target database.

Data Type	Description	Supported
VARCHAR2(size [BYTE \ CHAR])	Variable-length character string having maximum length size bytes or characters.	✓
NVARCHAR2(size)	Variable-length Unicode character string having maximum length size characters.	✓
NUMBER [ (p [, s]) ]	Number having precision p and scale s.	✓
FLOAT [(p)]	A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER.
LONG	Character data of variable length up to 2 gigabytes, or 231 -1 bytes.	✓
DATE	Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.	✓
BINARY_FLOAT	32-bit floating point number.	✓
BINARY_DOUBLE	64-bit floating point number.	✓
TIMESTAMP [(fractional_seconds_precision)]	Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.	✓
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.	✓
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	All values of TIMESTAMP WITH TIME ZONE, with exceptions.	✓
INTERVAL YEAR [(year_precision)] TO MONTH	Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]	Stores a period of time in days, hours, minutes, and seconds, where day_precision is the maximum number of digits in the DAY datetime field.
RAW(size)	Raw binary data of length size bytes.	✓
LONG RAW	Raw binary data of variable length up to 2 gigabytes.	✓
ROWID	Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn.
UROWID [(size)]	Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID.
CHAR [(size [BYTE \ CHAR])]	Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters.	✓
NCHAR[(size)]	Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.	✓
CLOB	A character large object containing single-byte or multibyte characters.
NCLOB	A character large object containing Unicode characters.
BLOB	A binary large object.
BFILE	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
JSON	Maximum size is 32 megabytes.

Parent topic: Replicating Oracle data