How To Replicate 'oracle sdo_geometry' Datatype With Local Data Processing

How To: Replicate ORACLE SDO_GEOMETRY datatype with HVR

Overview

For certain datatypes like Oracle SDO_GEOMETRY HVR uses "extended datatype support" instead of native datatype support. Extended datatype support requires a bit of extra configuration in the channel

Here is how to setup replication (including capture and refresh) for SDO_GEOMETRY

Note that extra configuration is needed to replicate these columns.

In this example have a table PRODUCT with a SDO_GEOMETRY column

SQL> describe product;

Name               Null?    Type
PROD_ID            NOT NULL NUMBER(11)
PROD_PRICE         NOT NULL NUMBER(10,2)
PROD_DESCRIP       NOT NULL VARCHAR2(20)
GEOM               PUBLIC.SDO_GEOMETRY

Steps:

Step 1] After the table is added to the channel you will see that the GEOM column is added with the channel but the datatype has special markers (<< >>) around it. This indicates that this is an extended datatype

Step 2] The following action need to be added to allow HVR to replicate the column:

{ group=* table=product ColumnProperties

/DatatypeMatch=<<sdo_geometry >> /CaptureExpression=SDO_UTIL.TO_WKTGEOMETRY({{hvr_col_name}}) /CaptureExpressionType=SQL_WHERE_ROW /IntegrateExpression=SDO_UTIL.FROM_WKTGEOMETRY({{hvr_col_name}}}