July 11, 2013 at 2:53 am
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
July 11, 2013 at 10:39 am
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.
July 11, 2013 at 10:49 am
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...
July 11, 2013 at 10:55 am
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.
July 11, 2013 at 11:04 am
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...
July 11, 2013 at 12:23 pm
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
July 12, 2013 at 2:36 am
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!;-)
July 12, 2013 at 1:10 pm
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.
July 15, 2013 at 12:27 am
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
July 15, 2013 at 8:14 am
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! :-):-)
July 15, 2013 at 8:25 am
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.
July 15, 2013 at 9:22 am
Thanks!! 🙂 I never give up before I solve the Problem. ;-):cool::hehe:
July 16, 2013 at 7:03 am
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