Hi,

I am reviewing the supported / unsupported data types within Attunity Oracle CDC for SSIS as part of a proof of concept. I would like clarification on the Oracle SDO_GEOMETRY data type. I know that it's not supported but I don't understand the behaviour.

Going by MSDN: Oracle CDC Instance Data Types

Source Oracle tables with columns of the following Oracle data types cannot be captured.

  • BFILE
  • ROWID
  • REF
  • UROWID
  • Nested Table

If the following data types are present in a table they will prevent the LogMinder from getting any data for any column of the table:

  • User-defined data types
  • VARRAY
It suggests that a user defined data type will break CDC if a column of this type exists on a table you are trying to do CDC on. I tested this by creating the following table in Oracle and setting up CDC:

Code:
CREATE TABLE GEOMETRY_TEST
(PK NUMBER(14,5),
DESCR VARCHAR2(20),
DATACOL MDSYS.SDO_GEOMETRY
)
I was expecting this to break CDC when I configured it due to the presence of the SDO_GEOMETRY column. It isn't a supported type therefore I couldn't capture changes in it, but CDC for the other two columns still worked. For reference SDO_GEOMETRY is defined as follows by Oracle:

Code:
CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER, 
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);
So back to my original point. Going by MSDN, the presence of this column should break CDC but it doesn't. I need to understand this behaviour before I can recommend to my client that this is fit for purpose (i.e. I don't want it to break further down the line because I haven't understood a limitation).

Could someone shed some light on this?

  • Is SDO_GEOMETRY just treated as a nested table (i.e. Wont be captured but wont break CDC)
  • Is it treated as a user defined data type (i.e. will break CDC for that table).


Thanks a lot in advance.

David