How SharePlex handles DDL replication in an open target environment? (4302352)

How SharePlex handles DDL replication in an open target environment?

Is DDL replication supported in Open Target environment?

General information.

SharePlex only supports DDL replication for objects in replication for Open Targets and not allow expanded DDL support (which is another term for DDL replication for objects not in replication). For the former, the only DDL which are supported are TRUNCATE, ALTER TABLE to ADD COLUMN or DROP COLUMN.

The reason only these DDLs are supported for objects in replication is, Open Targets come in all forms including databases that may have similarities with Oracle and others that may not. The basic DDLs which are supported will work for all targets as any database will allow adding, dropping of columns as well as TRUNCATE of tables. As for other DDLs that are supported in Oracle targets for objects in replication, they may or may not be applicable in some of the Open Targets. For this reason, the DDL replication for tables in replication is restricted to the ones listed above. One thing to keep in mind is that such DDL replication involves a variety of target databases, each having its own datatype corresponding to the datatype of Oracle which may be identical to or different from Oracle datatype. For example, in Oracle we have VARCHAR2 but in SQL Server the corresponding datatype is VARCHAR. Likewise Oracle has CHAR and so does SQL Server. Another non-Oracle target may yet have a different datatype for these. So SharePlex follows a principle of DDL mapping for various datatype for each of the Open Targets. As an example, you will find such mapping for DDL replication from Oracle to SQL Server in the Additional Information section below. Likewise there are many other mappings defined for other Open Targets. It is also possible to override such mappings and define one’s own mapping. Please refer to our Knowledgebase article # 224745 for details.

As for the expanded DDL support, the Open Targets offer even more complexities. The Open Targets come in all forms and each one will differ from Oracle in many respects. So to allow DDL for non-replicated objects is not feasible. Even in Oracle to Oracle replication expanded DDL support has lot of preconditions.

Please refer to Release Notes section titled “Oracle-to-Open Target DDL support” for details  on DDL support. 

Here is the mapping table for Oracle to SQL Server DDL replication as taken from the Release Notes section titled "Supported Open Target data types and DDL mappings": Oracle to SQL Server Oracle Data Type Mapped to SQL Server Data Type ANYDATA sql_variant BINARY_DOUBLE float BINARY_FLOAT float BLOB image CHAR char CLOB text DATE datetime2 LONG text LONGRAW image NCHAR nchar NCLOB ntext NUMBER numeric NVARCHAR nvarchar RAW varbinary TIMESTAMP WITH TIME ZONE datetimeoffset TIMESTAMP datetime2 VARCHAR(2) varchar