Specify Data Type Mappings for an Oracle Publisher

This topic describes how to specify data type mappings for an Oracle Publisher in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Although a set of default data type mappings are provided for Oracle Publishers, it might be necessary to specify different mappings for a given publication.

For some data types there is only one possible mapping, in which case the column in the property grid is read-only.

For some types, there is more than one type that you can select. Microsoft recommends that you use the default mapping unless your application requires a different mapping. For more information, see Data Type Mapping for Oracle Publishers.

You can specify custom data type mappings programmatically using replication stored procedures. You can also set the default mappings that are used when mapping data types between Microsoft SQL Server and a non-SQL Server database management system (DBMS). For more information, see Data Type Mapping for Oracle Publishers.

To define custom data type mappings when creating an article belonging to an Oracle publication

At the Distributor, execute sp_helparticlecolumns to view the existing mapping for a column in a published article.

At the Distributor, execute sp_changearticlecolumndatatype. Specify the name of the Oracle Publisher for @publisher, as well as @publication, @article, and @column to define the published column. Specify the name of the SQL Server data type to map to for @type, as well as @length, @precision, and @scale, where applicable.

At the Distributor, execute sp_articleview. This creates the view used to generate the snapshot from the Oracle publication.

To specify a mapping as the default mapping for a data type

(Optional) At the Distributor on any database, execute sp_getdefaultdatatypemapping. Specify @source_dbms, @source_type, @destination_dbms, @destination_version, and any other parameters needed to identify the source DBMS. Information on the currently mapped data type in the destination DBMS is returned using the output parameters.

(Optional) At the Distributor on any database, execute sp_helpdatatypemap. Specify @source_dbms and any other parameters needed to filter the result set. Note the value of mapping_id for the desired mapping in the result set.