Attunity CDC Service Between SQL2012 and Oracle

  • 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

  • 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.

  • 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...

  • 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.

  • 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...

  • 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

  • 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..:doze: 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!;-)

  • 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. :w00t:

    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.

  • 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

  • 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 :doze::crazy:

    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...:cool:

    I will keep you informed!!

    Thanks guys! :-):-)

  • Excellent. I'm glad you got it. So the wrong code page was being used in the conversion. With the new NLS setting, it works. Good job.

  • Thanks!! 🙂 I never give up before I solve the Problem. ;-):cool::hehe:

  • Hello!

    After changing the Original Oracle Database and setting NLS_NCHAR_CHARACTERSET = UTF8 it works in the production Database too! Uff!

    Thank God! I hope it helps someone, someday! :hehe:

    Regards,

    Rena

Viewing 13 posts - 1 through 12 (of 12 total)

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