Problem description
===================
I am trying to replicate from Oracle (10.2.0.2) on Red Hat to SQL Server 2005 SP2 on Windows 2003.
During the setup (new Oracle Publication) a get an error.
Cannot publish the table 'CRM5.X_CONTACT' from the Publisher 'SOT'. Verify connection information and ensure that you can connect to the Publisher through a tool like SQL*PLUS. Ensure that the replication administrative user schema has the required permissions.
Cannot add the Publisher triggers and the article log table to the Oracle Publisher for the article 'X_CONTACT'. Verify connection information and ensure that you can connect to the Publisher through a tool like SQL*PLUS. Ensure that the replication administrative user schema has the required permissions.
Changed database context to 'distribution_db'.
OLE DB provider "MSDAORA" for linked server "SOT" returned message "ORA-00936: missing expression
ORA-06512: at "REPL_ADMIN.HREPL", line 1722
ORA-06512: at line 1
".
Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'SOT'. (.Net SqlClient Data Provider)
>>>>
Everything regarding connectivity and privileges is ok.
>>>>
I have traced the interaction with Oracle and the failing code looks like this:
begin HREPL.PublishTable(
'CRM5','X_CONTACT',7,0,0,'
HREPL_ARTI
CL@E_7_7',
0,'2007-06
-22.15:45:
20.420',2,
'0000000000000000000000000
0@00000000
0000000000
0000000000
0000000000
0000000000
0000000000
000000@000
0000000000
0000000000
0000000000
0000000000
0000000000
0000000000
0@00000000
0000000000
0000000000
0000000000
0000000000
0000000000
000000@000
0000000000
0000000000
00000000ff
fffff',
'00000000000000000000000@0
0000000000
0000000000
0000000000
0000000000
0000000000
0000000000
000@000000
0000000000
0000000000
0000000000
0000000000
0000000000
00000000@0
0000000000
0000000000
0000000000
0000000000
0000000000
0000000000
0007000000
0000000000
0000000000
00000000ff
fffff',
NULL);
end;
/
>>>>
PROCEDURE PUBLISHTABLE
Argument Name Type In/Out Default?
--------------------------
---- ----------------------- ------ --------
ARGTABLEOWNER VARCHAR2 IN
ARGTABLENAME VARCHAR2 IN
ARGTABLEID NUMBER IN
ARGTRIGGERSTYLE NUMBER IN
ARGRECREATETRIGGERS NUMBER IN
ARGARTICLEVIEW VARCHAR2 IN
ARGREPLFREQ NUMBER IN
ARGTIMESTAMP VARCHAR2 IN
ARGINSTANCE NUMBER IN
ARGCOLUMNMASK RAW IN
ARGTRIGGERMASK RAW IN
ARGFILTERCLAUSE LONG IN
>>>>
I think Oracle does implicit conversion of RAW to a HEX string ... the Oracle sql*net trace of the interaction
between SQL and Oracle indicates that this is where the problem is. Ie, a RAW in a procedure's paramter list needs to be a valid HEX string.
Test case, in sqlplus on the Oracle system (string from trace):
declare
argColumnMask RAW(1024);
begin
argColumnMask := '0000000000000000000000000
0@00000000
0000000000
0000000000
0000000000
0000000000
0000000000
000000@000
0000000000
0000000000
0000000000
0000000000
0000000000
0000000000
0@00000000
0000000000
0000000000
0000000000
0000000000
0000000000
000000@000
0000000000
0000000000
00000000ff
fffff';
end;
/
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 4
Test case, in sqlplus on the Oracle system ("pure" hex string):
declare
argColumnMask RAW(1024);
begin
argColumnMask := '0123456789abcdef';
end;
/
PL/SQL procedure successfully completed.
>>>>
SQL Server Connection settings that are enabled:
ansi warning
ANSI NULL
Theories
========
a) I suspect that maybe the bit pattern for the column and trigger list (argColumnMask & argTriggerMask) is not correctly generated. I believe that the stored procedure sp_ORAddarticle is performing the actual work, but I have not been able to locate this procedure. Google (oops Live Search) on sp_ORAaddarticle yields no results.
The table I am trying to replicate has 28 columns -- I asked for all; that should result in 28/4 = 7 times 'f' in the column string. In addition there are extra characters ('@' and '7'): 3+1 and 4+0 respectively. I am speculating that these extra characters are added due errors. Ie, the maximum number of columns that can be replicated per table is (256*4 =) 1024.
b) It is also possible that there is something in the character sets (unicode vs whatever) that causes this error. I do not have access to a complete list of Oracle databases (8i, 9i, 10g) on the various platforms (Linux, Windows, etc), so I have not tested all the possible combinations ... also, I really do not know what I am looking for.
However, I have tried to run the Oracle DB (ver 10.2.0.3) on Windows 2003 (same node as SQL). Have also tried creating the Oracle db with Unicode before replicating: same result as described above.
Help
====
I am looking for any help at all on this issue. Does the Oracle to SQL replication really work? Did it work in 2005 RTM or SP1, etc? Does work on 8i/9i but not with 10g?
Thx,
Espen