Specify Data Type Mappings for an Oracle Publisher

Article
03/03/2023

Applies to: SQL Server

This topic describes how to specify data type mappings for an Oracle Publisher in SQL Server 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.

In This Topic

To specify data type mappings for an Oracle Publisher, using:

SQL Server Management Studio

Transact-SQL

Using SQL Server Management Studio

Specify data type mappings on the Data Mapping tab of the Article Properties - <Article> dialog box. This is available from the Articles page of the New Publication Wizard and the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see Create a Publication from an Oracle Database and View and Modify Publication Properties.

To specify a data type mapping

On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then click Article Properties.
Click Set Properties of Highlighted Table Article.
On the Data Mapping tab of the Article Properties - <Article> dialog box, select mappings from the Subscriber Data Type column:
- 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.
Select OK.

Using Transact-SQL

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

If one does not already exist, create an Oracle publication.
At the Distributor, execute sp_addarticle. Specify a value of 0 for @use_default_datatypes. For more information, see Define an Article.
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.
At the Distributor on any database, execute sp_setdefaultdatatypemapping.
- If you know the desired value of mapping_id obtained in step 2, specify it for @mapping_id.
- If you do not know the mapping_id, specify the parameters @source_dbms, @source_type, @destination_dbms, @destination_type, and any other parameters required to identify an existing mapping.

To find valid data types for a given Oracle data type

At the Distributor on any database, execute sp_helpdatatypemap. Specify a value of ORACLE for @source_dbms and any other parameters needed to filter the result set.

Examples (Transact-SQL)

This example changes a column with an Oracle data type of NUMBER so it is mapped to SQL Server data type numeric(38,38), instead of the default data type float.

EXEC sp_changearticlecolumndatatype 
    @publication = 'OraPublication', 
    @publisher = 'OraPublisher', 
    @article = 'OraArticle', 
    @column = 'OraArticleCol', 
    @type = 'numeric', 
    @scale = 38, 
    @precision = 38;
GO

This example query returns the default and alternative mappings for the Oracle 9 data type CHAR.

EXEC sp_helpdatatypemap 
    @source_dbms = N'ORACLE', 
    @source_version = 9,
    @source_type = N'CHAR';
GO

This example query returns the default mappings for the Oracle 9 data type NUMBER when it is specified without a scale or precision.

EXEC sp_helpdatatypemap 
    @source_dbms = N'ORACLE', 
    @source_version = 9,
    @source_type = N'NUMBER',
    @defaults_only = 1;
GO