Error message when the Distribution Agent tries to apply to the initial snapshot to an Oracle 11g subscriber: “ORA-00932: inconsistent datatypes”

Transactional replication from SQL Server 2005 to Oracle 11g server. In this scenario, when delivering the initial snapshot to the subscriber, the distribution agent may receive an error message that is similar to the following:

Agent message code 932. ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBER

The details of the error message will be similar to the following:

ErrorText = ‘ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBER’

Message: Replication-Replication Distribution Subsystem: agent <distribution agent name> failed. ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBERErrorId = 1403, SourceTypeId = 16

ErrorCode = ‘932’

ErrorText = ‘ORA-00932: inconsistent datatypes; expected : INTERVAL DAY TO SECOND ; got: NUMBER’

Category:NULL

Source: OraOLEDB

Number: 932

Note: The issue does not occur when setting up replication between

  • SQL Server 2005 and Oracle 10g
  • SQL Server 2008 Service Pack 1 and Oracle 11g

The issue occurs because the SQL Server does not define any data mapping to Oracle 11g servers and hence the MSREPL7 table that gets created on the Oracle server replication setup has an erroneous schema.

If you do a desc on MSREPL7 on Oracle server, the schema will look as follows:

pubsrv NVARCHAR2(128)
pubdb NVARCHAR2(128)
publcn NVARCHAR2(128)
indagent NUMBER(1)
subtype INTERVAL DAY(2) TO SECOND(6)
dstagent NVARCHAR2(100)
timecol TIMESTAMP(6)
descr NVARCHAR2(255)
xactts BINARY FILE LOB
updmode INTERVAL DAY(2) TO SECOND(6)
agentid BINARY FILE LOB
subguid BINARY FILE LOB
subid BINARY FILE LOB
immsync NUMBER(1)

The schema should have been created as follows:


pubsrv NVARCHAR2(128)
pubdb NVARCHAR2(128)
publcn NVARCHAR2(128)
indagent NUMBER(1)
subtype NUMBER(10)
dstagent NVARCHAR2(100)
timecol DATE
descr NVARCHAR2(255)
xactts RAW(16)
updmode NUMBER(3)
agentid RAW(16)subguid RAW(16)
subid RAW(16)
immsync NUMBER(1)

To fix recreate the table as:

DROP TABLE SCHEMA.MSREPL7 CASCADE CONSTRAINTS;

CREATE TABLE SCHEMA.MSREPL7
(
PUBSRV    VARCHAR2(128 BYTE),
PUBDB     VARCHAR2(128 BYTE),
PUBLCN    VARCHAR2(128 BYTE),
INDAGENT  NUMBER(1),
SUBTYPE   NUMBER(10),
DSTAGENT  VARCHAR2(100 BYTE),
TIMECOL   DATE,
DESCR     VARCHAR2(255 BYTE),
XACTTS    RAW(16),
UPDMODE   NUMBER(3),
AGENTID   RAW(16),
SUBGUID   RAW(16),
SUBID     RAW(16),
IMMSYNC   NUMBER(1)
)

Advertisements

One Response to Error message when the Distribution Agent tries to apply to the initial snapshot to an Oracle 11g subscriber: “ORA-00932: inconsistent datatypes”

  1. Pingback: SQL Server to Oracle Replication Troubleshooting « Mohsin's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: