Supported Oracle data types

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 in the target database 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).

You can use the unsupported_type_action parameter to choose how to handle replicating tables that contain columns with unsupported datatypes. Set the unsupported_type_action parameter to one of the following values:

  • filter_object: Source tables containing unsupported fields or columns are not replicated to the target database.

  • filter_field : Unsupported fields or columns in source tables are filtered out and not replicated to the target database.

The following table shows the Oracle data types that you can replicate:

Data type Supported Description
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 2³¹-1 bytes.
DATE ✓ Valid date range from January 1, 4712 BC, to December 31, 9999 AD. Format is determined by NLS_DATE_FORMAT or NLS_TERRITORY. Includes YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. No fractional seconds or time zone.
BINARY_FLOAT ✓ 32-bit floating point number.
BINARY_DOUBLE ✓ 64-bit floating point number.
TIMESTAMP [(fractional_seconds_precision)] ✓ Includes YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and fractional seconds. No time zone.
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE ✓ Includes all TIMESTAMP fields plus TIMEZONE_HOUR and TIMEZONE_MINUTE. Has fractional seconds and 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. year_precision is the number of digits in the YEAR field.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] ✓ Stores a period of time in days, hours, minutes, and seconds. day_precision is the max digits in the DAY 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. Used for values returned by the ROWID pseudocolumn.
UROWID [(size)] ✓ Base 64 string representing the logical address of a row in an index-organized table. Optional size defines column size.
CHAR [(size [BYTE \ CHAR])] ✓ Fixed-length character data of length size bytes or characters. Max size is 2000 bytes or characters.
NCHAR[(size)] ✓ Fixed-length character data of length size characters. Byte size varies by encoding: up to 2×size for AL16UTF16, 3×size for UTF8.
CLOB ✓ Character large object containing single-byte or multibyte characters.
NCLOB ✓ Character large object containing Unicode characters.
BLOB ✓ Binary large object.
BFILE Locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs. Max size is 4 GB.
JSON Maximum size is 32 megabytes.