[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.0

Error in Oracle to Microsoft SQL replication

Asked by ebraekke in SQL Server 2005, MS SQL Server, Oracle Database

Tags: hex, raw, conversion

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_ARTICL@E_7_7',0,'2007-06-22.15:45:20.420',2,
'00000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000fffffff',
'00000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@000000000000000000000000000000000000000000000000000000000000000070000000000000000000000000000000000fffffff',
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 := '00000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000000000000000000000000000000000000@0000000000000000000000000000000fffffff';
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
[+][-]06/26/07 03:54 AM, ID: 19362432Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: SQL Server 2005, MS SQL Server, Oracle Database
Tags: hex, raw, conversion
Sign Up Now!
Solution Provided By: imran_fast
Participating Experts: 1
Solution Grade: C
 
[+][-]06/26/07 05:35 AM, ID: 19363009Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06/26/07 05:45 AM, ID: 19363069Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]06/26/07 06:55 AM, ID: 19363672Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07/08/07 01:17 AM, ID: 19439473Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091021-EE-VQP-81