• Article

How to: Specify Data Type Mappings for an Oracle Publisher (Replication Transact-SQL Programming)

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. 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

  1. If one does not already exist, create an Oracle publication. For more information, see How to: Create a Publication from an Oracle Database (Replication Transact-SQL Programming).

  2. At the Distributor, execute sp_addarticle. Specify a value of 0 for @use_default_datatypes. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

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

  4. 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.

  5. 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

  1. (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.

  2. (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.

  3. 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.

Example

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