Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Attunity CDC Service Between SQL2012 and Oracle Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 2:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
Hello everyone,

I work the last days on a Project where I must transfer Data from an Oracle Database to a Sql Server 2012 Database. The CDC Service works fine and it captures all changes on the Source Database but the Data when transfered to the Destination DB, to our beloved SQL Server 2012, it is altered!!!
See, the source Data on the Oracle DB = '164f3293-e5fa-435e-a15d-f67ab12b1486'
and the Data on the Destination = '??????????????????'
Why?

Thank you a lot!

Regards,
Rena
Post #1472474
Posted Thursday, July 11, 2013 10:39 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 4,243, Visits: 3,676
It looks to me like you're trying to insert a GUID data type into a Varchar or Varchar2 data type without doing a data type conversion. Start by checking the data types of the fields involved, then do the appropriate conversion in the SELECT statement in the transfer.

Generally, you'll need to convert anything that is specific to one database platform and not the other.

The Oracle conversion document is at http://docs.oracle.com/html/B10544_01/apa.htm.
The MSDN conversion document is at http://msdn.microsoft.com/en-us/library/ms151817.aspx.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1472699
Posted Thursday, July 11, 2013 10:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
Using the etl tools for many years, that was the first thing that i checked. The source column type is varchar2 and the destination is varchar...
Post #1472703
Posted Thursday, July 11, 2013 10:55 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 4,243, Visits: 3,676
The Oracle Varchar2 data type supports Unicode and the SQL Server Varchar data type does not. That completely explains the '???' in the fields. You can change your destination field from Varchar to NVarchar or do a conversion in the SELECT.

I've encountered the '???' myself when importing things from glyph-based languages.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1472705
Posted Thursday, July 11, 2013 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
Thats great that you have experience so you can help me!! Hm the problem is that i always use nvarchar on the sql server. And the sql server knows that and the first script that was generated was that the destinstion column type should be nvarchar!! So, the first time i runned the cdc service the destination characters were in chinese!! Believe me or not!! I will sent you an example in one hour! The problem is that i am an oracle newbie...
Post #1472711
Posted Thursday, July 11, 2013 12:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
Hello again and thank you really for your help!

Here 2 examples:

Source Type, Source Value, Destination Type , Destination Value

Varchar2, '164f3293-e5fa-435e-a15d-f67ab12b1486', nvarchar, ‘ㄶ㑦㌲㤳ⵥ㕦愭㐳㕥ⵡㄵ搭昶㝡戱㉢ㄴ㠶’

Varchar2, '164f3293-e5fa-435e-a15d-f67ab12b1486', varchar, ‘??????????????????’

Great!!!

Here the results after I run this query on the Oracle DB:

select parameter,value from v$nls_parameters


PARAMETER    VALUE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY <st1:country-region w:st="on"><st1:place w:st="on">AMERICA</st1:place></st1:country-region>

NLS_CURRENCY $

NLS_ISO_CURRENCY <st1:country-region w:st="on"><st1:place w:st="on">AMERICA</st1:place></st1:country-region>

NLS_NUMERIC_CHARACTERS .,

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_CHARACTERSET AL32UTF8

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

Post #1472731
Posted Friday, July 12, 2013 2:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
Hello and for your info,

it works when the source column type on the Oracle Database is nvarchar2 and i get the right data on the SQL Server Tables...
As an Oracle newbie, I dont know if it has to do with the configuration of the Oracle Database or something similar.. I will check it.

Thanks for your help That’s why I love the SQL Server! We are a great community and help each other!
Post #1472905
Posted Friday, July 12, 2013 1:10 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 4,243, Visits: 3,676
Okay, please forgive me for this, but I don't have access to an Oracle database. That was quite a few years ago and I'm trying to dust off memory cells. I hope I get this right. I would really like to post the code to do the appropriate conversion, but can't even try it without an Oracle database. In short, I'm going off memory here.

I don't think TO_CHAR will get you what you're after, since the field is already a Varchar2. So what if you used SELECT CAST(source_file AS Varchar) in the Oracle SELECT? Does the value survive the transfer? If the original value is anything but a string string type, this should equalize things out.

I don't know of a situation that causes the '???' in the SQL Server field other than characters being populated that aren't appropriate for the data type. I'm thinking it's because on the Oracle side of things, the data type being fed to SQL Server isn't known by SQL Server, so it just populates '???' instead of throwing an error.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1473218
Posted Monday, July 15, 2013 12:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
Hello Ed Wagner and really thanks for any help!
The problem is that i am using the sql server 2012 Attunity service and i cant do any select on the source or destination. But I can define the type that the destination column will have. So i tried nvarchar and varchar and you can see the rusults... Using a ssis to transfer tha data there is not a probleme and without using a transform task. See what I have noticed... The ssis creates the column types varchar2 on the source as nvarch types on the destination. But using another oracle instance with the same schema|database it crates the destination columns as varchar. The difference between them is that the first has national encondin as 32utf16 and the second utf8... i must try this service with the second instance.
Uff! Wish me good luck!!
Rena
Post #1473466
Posted Monday, July 15, 2013 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 39, Visits: 288
FYI!

It was a pain to create a Database in Oracle... I have not the logic Database = User = Schema... I don’t get it even now... and I am confused
I succeeded and the new Oracle Database/Schema has NLS_NCHAR_CHARACTERSET =UTF8. Now it works!!! The CDC service gets the right data without the weird Chinese characters or question marks... I dont understand why that did happen but wish me luck that it works on the Original DB and data. The Oracle dba's will hate me... It’s like to change the SQL Server Collation...
But I cant be sure if it will work on the Original Schema after this change...
I will keep you informed!!
Thanks guys!


Post #1473670
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse