ORA-00932: inconsistent datatypes: expected FILE got BINARY when Initializing SQL 2k8R2 Publication to Oracle 12c Sub

  • raz.the.cat

    Old Hand

    Points: 322

    I'm attempting to setup a Non-SQL subscriber (Oracle 12c) on a SQL 2k8R2 instance. Once I setup the subscriber and the agent attempts to start replicating between the SQL and 12c server, I get the ORA-00932: inconsistent datatypes: expected FILE got BINARY error. The agent will create the MSREPL7 table on the Oracle instance so I know there connection is good and the credentials being used have permission to the Oracle instance.

    I've tried changing the data type on the MSREPL7 table on the Oracle server to RAW for anything that was BFILE as a shot in the dark (new to Oracle) but that didn't help. Just flipped the expected/got error.

    Has anyone here run into this issue? What am I missing?

    SQL Version - Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Oracle version - -------------------------------------------------------------------------------- ----------

    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

    PL/SQL Release 12.1.0.1.0 - Production 0

    CORE 12.1.0.1.0 Production 0

    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production 0

    NLSRTL Version 12.1.0.1.0 - Production

  • dataonfocus

    SSC Enthusiast

    Points: 176

    Similar issue here, anyone knows?

    Thanks

  • dataonfocus

    SSC Enthusiast

    Points: 176

    Anyone??

  • @Ashish

    Old Hand

    Points: 383

    Similar Issue here. Did you able to solve your problem?

    _________

    Regards,
    Ashish

  • SQLKnight

    Old Hand

    Points: 316

    The below has worked for me. You can give it a try in your lower environments if it permits.

    https://dbamohsin.wordpress.com/category/sql-server/page/16/

    Thanks

  • @Ashish

    Old Hand

    Points: 383

    I contacted Microsoft support. They agree that it does have to do with Oracle12c upgrade and provided workaround for the issue.

    FROM DAC Connection:

    Workaround to add oracle publisher support w/o new datatype support:

    exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

    exec sys.sp_MSrepl_ORA_MSSQL_datatypemappings @source_dbms = N'ORACLE', @source_version = '12', @destination_dbms = N'MSSQLServer'

    Warkaround to add oracle subscriber support w/o new datatype support:

    exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

    exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'

    ---

    This worked for me and hopefully will help somebody else too!

    Thanks!

    _________

    Regards,
    Ashish

  • nugentgregg

    SSC Rookie

    Points: 29

    The most recent era of the world's #1 database, Oracle Database 12c is accessible on a decision of stages. Prophet Database 12c Enterprise Edition highlights more than 500 new components including another building design that streamlines the procedure of solidifying databases onto the cloud, empowering clients to oversee numerous databases as one without changing their applications.For get a clear expert advice please choose thesis writing service[/url] provider.

  • cbrown 29958

    SSC Journeyman

    Points: 87

    Hello,

    I am running SQL Server 2008 (SP4) - 10.0.6000.29 (X64). I am experiencing a problem attempting to create a replication article with Oracle 12c as Publisher. The workaround solution mentioned in this thread requires 2 stored procedures below.

    1. sys.sp_MSrepl_ORAdatatypes

    2. sys.sp_MSrepl_ORA_MSSQL_datatypemappings

    I receive the following errors when I execute these procedures. Where are these procedures?

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sys.sp_MSrepl_ORAdatatypes'.

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sys.sp_MSrepl_MSSQL_ORA_datatypemappings'.

  • Ronzo

    Hall of Fame

    Points: 3730

    Most likely you were not trying to run the stored procedures from a DAC connection. Lookup DAC in SQL Server Books On Line for more information.


    Have Fun!
    Ronzo

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply