Oracle spatial datatype limitations - connect_cdc_mimix_share - Latest

Connect CDC System Reference Guide
Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
Latest
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
Last edition
2024-08-20
Last publish date
2024-08-20T21:40:14.000381

The Oracle spatial datatypes have the following limitations:

  • The prepared statement option for a table is not allowed when one or more of the spatial datatype columns are mapped.

  • Spatial datatypes may not be specified as a distribution key column in a table mapping.

  • Spatial datatypes cannot be referenced in expressions.

  • Oracle spatial columns may only be replicated to target columns that are defined as either Geography or Geometry in MS SQL Server.

  • Connect CDC does not support spatial datatypes defined as SOLID or MULTISOLID

  • Only trigger based capture is supported for Oracle 11g.

  • Using ST_GEOMETRY objects after running the Oracle database upgrade script, sdoupggeom.sql, generates a size overflow exception. The ST_GEOMETRY overflow limitation is inherent to Oracle JDBC spatial support and occurs in all Oracle databases with an expanded ordinates array. This limitation does not apply to SDO_GEOMETRY objects.

Oracle Source Datatype

Supported Targets

SMALLINT

DB2 (includes IBM i and UDB)

  • BIGINT

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • SMALLINT
 

Informix

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • SMALLFLOAT

  • SMALLINT
 

MS SQL Server/SQL Azure

  • BIGINT

  • BIT

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • SMALLINT

  • TINYINT
 

Oracle

  • NUMBER

  • NUMBER(38)

  • NUMBER (p,s). Connect CDC Director warns.

  • VARCHAR2 (L), where L>= 6.
 

Sybase

  • BIT

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • SMALLINT

  • TINYINT

SMALLINT

Teradata

  • BIGINT

  • BIT

  • BYTEINT

  • DECIMAL(p,s)

  • FLOAT

  • INTEGER

  • NUMBER

  • LONG VARCHAR

  • SMALLINT
 

All Platforms

  • CHAR (L), where L>= 6.

  • VARCHAR (L), where L>= 6.

 

Oracle Source Datatype

Supported Targets

TIMESTAMP

Note:  Oracle supports the TIMESTAMP datatype with a fractional second precision up to 9 digits. Connect CDC only a supports a fractional second precision up to a maximum of 6 digits, the default.

DB2 (includes IBM i and UDB)

  • TIMESTAMP
 

Informix

  • DATETIME. Ideal mapping is to DATETIME (YEAR TO SECOND).
 

MS SQL Server/SQL Azure

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME
 

Oracle

  • TIMESTAMP

  • VARCHAR2 (L), where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.
 

PostgreSQL

  • DATE

  • TIME

  • TIMESTAMP
 

Sybase

  • DATETIME
 

Teradata

  • LONG VARCHAR

  • TIMESTAMP

TIMESTAMP

All Platforms

  • CHAR (L)

  • VARCHAR (L)

where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.
 

Note:

For the following datatypes, seconds and fractions of a second of time data are truncated.

  • DATETIME

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

Oracle Source Datatype

Supported Targets

TIMESTAMP(P)

DB2 (includes IBM i and UDB)

  • TIMESTAMP
 

Informix

  • DATETIME. Ideal mapping is to DATETIME (YEAR TO SECOND).
 

MS SQL Server/SQL Azure

  • DATETIME.

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME.
 

Oracle

  • TIMESTAMP

  • TIMESTAMP(P)

  • VARCHAR2 (L), where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.
 

PostgreSQL

  • TIME

  • TIMESTAMP
 

Sybase

  • DATETIME.

  • SMALLDATETIME.
 

Teradata

  • LONG VARCHAR

  • TIMESTAMP
 

All Platforms

  • CHAR (L)

  • VARCHAR (L)

where L >= 20. Format of target value is YYYYMMDDHHMMSSFFFFFF.
 

Note:

For the following datatypes, seconds and fractions of a second of time data are truncated.

  • DATETIME.

  • DATETIME2(P)

  • DATETIMEOFFSET(P)

  • SMALLDATETIME

Oracle Source Datatype

Supported Targets

VARCHAR

DB2 (includes IBM i and UDB)

  • CHAR (Unicode)

  • CLOB (IBM i only)

  • DBCLOB (Unicode IBM i only)

  • GRAPHIC (Unicode)

  • VARGRAPHIC (Unicode)

Informix

MS SQL Server/SQL Azure
 

Oracle

  • CLOB

  • LONG

  • VARCHAR2

Sybase
 

Teradata

  • LONG VARCHAR
 

All Platforms

  • CHAR

  • VARCHAR

Oracle Source Datatype

Supported Targets

VARCHAR2

DB2 (includes IBM i and UDB)

  • CHAR (Unicode)

  • CLOB (IBM i only)

  • DBCLOB (Unicode IBM i only)

  • GRAPHIC (Unicode)

  • VARGRAPHIC (Unicode)

Informix

MS SQL Server/SQL Azure
 

Oracle

  • CLOB

  • LONG

  • VARCHAR2

Sybase
 

Teradata

  • LONG VARCHAR
 

All Platforms

  • CHAR

  • VARCHAR

Oracle Source Datatype

Supported Targets

XMLTYPE

DB2 (includes IBM i and UDB)

  • CLOB

  • DBCLOB
 

MS SQL Server/SQL Azure

  • NTEXT

  • TEXT

  • NVARCHAR(MAX)

  • VARCHAR(MAX)

  • XML
 

Oracle

  • CLOB

  • NCCLOB

  • XMLTYPE
 

Sybase

  • TEXT