Replicating UDDs

To replicate UDDs in Oracle, you must add a datatype definition to Replication Server so the UDD is replicated exactly as it is executed in the primary database. UDDs from Oracle are sent to Replication Server as data for a single varchar column. By default, Replication Server wraps all varchar data in single quotation marks. In order to prevent Replication Server from adding these quotation marks to UDD data, a special datatype must be created in Replication Server and that datatype must be used as the datatype for any UDD column defined in a replication definition.

When you create a datatype definition in Replication Server, you must use an unused datatype ID. This is the DTID column of the rs_datatype table. The new datatype is a Replication Server datatype, so it will be available to all connections defined in the Replication Server that owns the Replication Server system database (RSSD); you only have to do this once each Replication Server instance.

To create a datatype definition in Replication Server

To create the datatype requires Replication Server administrator privileges or granted permission.

Add a row to the rs_datatype table using the following example as a guide:

/*  rs_oracle_udd_raw - char with no delimiters */
insert into rs_datatype values(
0,                   /* prsid */
0x0000000001000008,  /* classid */
'rs_oracle_udd',     /* name */
0x0000000000010210,  /* dtid */
0,                   /* base_coltype */
255,                 /* length */
0,                   /* status */
1,                   /* length_err_act */
'CHAR',              /* mask */
0,                   /* scale */
0,                   /* default_len */
'',                  /* default_val */
0,                   /*-delim_pre_len-*/
'',                  /* delim_pre */
0,                   /*-delim_post_len-*/
'',                  /* delim_post */
0,                   /* min_boundary_len */
'',                  /* min_boundary */
3,                   /* min_boundary_err_act */
0,                   /* max_boundary_len */
'',                  /* max_boundary_err_act */
0                    /* rowtype */
)
go

You must restart Replication Server after adding a new type.

In Replication Server, test the new type using the admin translate command:

admin translate, 'The quick brown fox jumped over the lazy dog.', 'char(255)', 'rs_oracle_udd'go

Delimiter Prefix   Translated                  Value Delimiter Postfix ---------------------------------------------------------------------NULL               The quick brown fox jumped over the lazy dog.  NULL

The new type has been defined correctly if the sentence was translated correctly.

Example

The following example demonstrates how to create a replication definition, using a new type defined in Replication Server. The following Oracle table and type definitions are used in the example:

Oracle UDD object type name: NAME_T
Oracle table name: USE_NAME_T
Oracle table columns: PKEY INT, PNAME NAME_T

create replication definition use_name_t_repdef
with primary at ra_source_db.ra_source_ds
with all tables named 'USE_NAME_T'
(
   PKEY int,
   PNAME rs_oracle_udd
)
primary key (PKEY)
searchable columns (PKEY)
go

The ltl_character_case must be in uppercase for this example.