1

While porting data from oracle to Azure sql database I am facing the following issue where, empty data in Oracle is converted as ➞➞➞ in SQL Server

Pic1: Data as in Oracle DB;

Screenshot data of Oracle Database

Pic2: Data as in Sql server DB;

Screenshot data of SQL Server Database

Pic3: Data when pasted in Notepad++.

Screenshot of the oracle data when pasted in Notepad++

The character set in Oracle is AL32UTF8 and that of target SQL Server database is SQL_Latin1_General_CP1_CI_AS. I have tried changing the datatype to NVARCHAR / NCHAR and changing the collation of the column to SQL_Latin1_General_CP850_CI_AS, but no solution. What is the reason for this difference and is there any solution?

Improve this question
2
  • 1
    Use rawtohex and dump in Oracle to see if the "empty" column contains non-printable characters.
    – vonPryz
    Commented Aug 12, 2020 at 8:48
  • 1
    Check the content in the Oracle database with select dump(name, 1016) from table where ... to see what is really stored in there.
    – Balazs Papp
    Commented Aug 12, 2020 at 9:50

1 Answer 1

Reset to default
2

NVARCHAR is always UTF-16.

You can try Latin1_General_100_CI_AS_SC_UTF8 with a VARCHAR column, but that doesn't look like an encoding problem. It really shouldn't matter what the source encoding / collation was as long as you correctly indicate the encoding used to transfer the data.

Either way, those 3 bytes / characters could be a bug in the import tool/process or the export tool/process. How are you transferring the data? If the image you are showing from Notepad++ is from the file being used to migrate the data and the field is supposed to be empty, either the export messed up, or perhaps the field in Oracle is not truly empty.

Improve this answer
10
  • I tried Latin1_General_100_CI_AS_SC_UTF8 with varchar, and yeah it didnt work. The column in oracle DB is actually 'not null', so it is not truly empty, but if i copy paste in sql developer i could see three empty space only and we are porting this data using Oracle Golden Gate.
    – Venkat
    Commented Aug 12, 2020 at 9:02
  • 1
    @Venkat By definition, a space is not "empty" ;-).Empty can only mean NULL or for strings, an empty string (i.e. having 0 characters). Can you paste those into Notepad++, then hit Control-A, go to Plugins menu, select Converter then ASCII -> HEX ? You should see "1A", which is character 26, which you can get from either Control-Z or while holding down ALT key, type 026 on keypad (in Notepad++ , not in a browser).
    – Solomon Rutzky
    Commented Aug 12, 2020 at 13:13
  • My bad, yeah, space is not empty, Thanks a lot for the explanation. So , after going through your explanation, I tried the following 'SELECT RAWTOHEX(name) "name_hex" FROM <Table_Name>' as you said the output was '1A1A1A', Also we are porting data via OGG, how to resolve this issue at sql server? can you let me know/or guide me to any link to understand more about this? Thanks a ton again.
    – Venkat
    Commented Aug 12, 2020 at 13:56
  • @Venkat First execute the following to see what's actually in that column: SELECT CONVERT(VARBINARY(10), [name]) FROM dbo.table_name;. Is it 0x1A1A1A? If yes, then there's no issue to resolve as the data in SQL Server is the same as it was in Oracle, correct? If not, then we need to figure out why not.
    – Solomon Rutzky
    Commented Aug 12, 2020 at 14:44
  • @Venkat Also, I suppose in either case you should probably choose the desired datatype and collation now. If you go with UTF-8, you should also choose that as the DB's default collation, use only VARCHAR for strings (no NVARCHAR), and only use _UTF8 collations. For a detailed look at various pros and cons of UTF-8 in SQL Server, please see: sqlquantumleap.com/2018/09/28/…
    – Solomon Rutzky
    Commented Aug 12, 2020 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.