none
SQL Server - Heterogeneous Replication RRS feed

  • Question

  • Hi,

    We currently have the below replication setup in SQL Server & it is working fine.

    SQL Server 2008 R2 is both “Publisher” and “Distributor” too.

    Oracle database 11g release 1 is “Subscriber”.

    Now, when we try to make Oracle database 18c as “Subscriber”, SQL Server throws the below error message:

    ORA-00932: inconsistent datatypes: expected FILE got BINARY (Source: <publisher name>, Error number: 932)

    Get help: http://help/932

    I assume we are getting this error because, SQL Server doesn’t have the datatype between Oracle18c & SQL Server.

    I executed the below command... and I only see Oracle database mapping till 11g.

    exec sp_helpdatatypemap

    @source_dbms = N'MSSQLSERVER',

    @destination_dbms = N'ORACLE'

    Can someone help me how to add Oracle database 18c mappings in SQL Server 2008 R2?


    Many thanks in advance!


    Friday, May 17, 2019 12:20 PM

Answers

All replies

  • See Non-SQL Server Subscribers: Up to Oracle 12c is supported

    Replication to Oracle is a deprecated feature


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    • Edited by Olaf HelperMVP Friday, May 17, 2019 12:55 PM
    • Proposed as answer by Puzzle_Chen Monday, May 20, 2019 2:28 AM
    • Marked as answer by ManushK Monday, May 20, 2019 8:36 AM
    Friday, May 17, 2019 12:54 PM
  • Per documentation, SQL Server 2016 or earlier supports replication to Oracle up to 12c. So I don't expect it to work well with beyond 12c versions.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/heterogeneous-database-replication?view=sql-server-2017


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by Puzzle_Chen Monday, May 20, 2019 2:28 AM
    • Marked as answer by ManushK Monday, May 20, 2019 8:36 AM
    Friday, May 17, 2019 1:00 PM
  • Hi Olaf Helper,

    Thank you for the URL. I now understand that replication feature may not work with subscriptions of Oracle database versions greater than 12c.

    But, I tried the same approach with both "12c release1" and "12c release2"  databases. But, it still did not work.

    One important observation is: exec sp_helpdatatypemap has Oracle version till 11g only. Is there a way we can manually update this data type map so that SQL Server 2008 R2 can replicate to Oracle database 12c?

    Is data type mapping the only problem (or) Am I missing some important configuration part in the big picture?

    Thanks again!


    • Marked as answer by ManushK Monday, May 20, 2019 8:35 AM
    • Unmarked as answer by ManushK Monday, May 20, 2019 8:36 AM
    Monday, May 20, 2019 8:35 AM
  • Hi Mohsin A Khan,

    Thank you for the response.

    I tried the same approach with both "12c release1" and "12c release2"  databases. But, it still did not work. Is there some configuration step to achieve replication to at least to Oracle 12c databases?

    Monday, May 20, 2019 8:39 AM