In a recent data migration test in my organization, we found that if we have Oracle data that looks like this: "ABCD<null>EF" then SSMA for Oracle migrates it to SQL Server as this: "ABCD<null><null><null>". Here "<null>" is the ASCII NULL character, or CHAR(0) of T-SQL or CHR(0) of Oracle SQL.

Oracle version: 12c "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production"
SSMA for Oracle version: 8.21

My Oracle test table is this:

drop table t_null;
create table t_null(x number, y varchar2(10));
insert into t_null (x, y)
select 1, 'ABCDE'                 from dual union all
select 2, 'ABCDE'||CHR(0)         from dual union all
select 3, 'ABCDE'||CHR(0)||'F'    from dual union all
select 4, 'ABCDE'||CHR(0)||'FGH'  from dual union all
select 5, 'ABCDE'||CHR(0)||'FGHI' from dual
commit;
select x, y, dump(y) as dmp from t_null;
After migration using SSMA for Oracle, I use this T-SQL query:
select x, y, convert(varbinary(1000), y) as y_vb from dbo.T_NULL;
and the results look like this:
2> select x, y, convert(varbinary(1000), y) as y_vb from dbo.T_NULL;
x                        y          y_vb
------------------------ ---------- ------------------------
                     1.0 ABCDE      0x4142434445
                     2.0 ABCDE      0x414243444500
                     3.0 ABCDE      0x41424344450000
                     4.0 ABCDE      0x414243444500000000
                     5.0 ABCDE      0x41424344450000000000
(5 rows affected)
Neither SSMS nor sqlcmd show the ASCII NULL character or anything to its right (even if that character is not ASCII NULL).  

However, the output of the CONVERT() function indicates that each character after the first ASCII NULL in the Oracle data has also been converted to ASCII NULL by SSMA.
Interestingly, if I insert such data manually in SQL Server, it does work. Only the display is not correct.
    drop table if exists dbo.t_null;
    create table dbo.t_null(x int, y varchar(10));
    insert into dbo.t_null(x, y)
    values
    (1, 'ABCDE'),
    (2, 'ABCDE'+CHAR(0)),
    (3, 'ABCDE'+CHAR(0)+'F'),
    (4, 'ABCDE'+CHAR(0)+'FGH'),
    (5, 'ABCDE'+CHAR(0)+'FGHI')
    select x, y, convert(varbinary(1000), y) as y_vb from dbo.t_null;
    -- Output
2> select x, y, convert(varbinary(1000), y) as y_vb from dbo.T_NULL;
x           y          y_vb
----------- ---------- ------------------------------------
          1 ABCDE      0x4142434445
          2 ABCDE      0x414243444500
          3 ABCDE      0x41424344450046
          4 ABCDE      0x414243444500464748
          5 ABCDE      0x41424344450046474849
(5 rows affected)
Is this a known bug in SSMA for Oracle 8.21?  

Has it been fixed in any higher version?
Thanks and regards,  

Homebrew9
What connectivity stack do you use when connecting to Oracle? Using latest SSMA v8.23 with ODP.NET I can see values are transferred as they should. I suspect there might be issues with ODBC/OLE DB, as these are native components and may use null-terminated strings to represent character data somewhere internally.  
If you were using ODP.NET already, then be aware that in v8.23 we upgraded ODP.NET to 19.13.0 (as opposed to 19.11.0 used previously). Maybe there were some bug fixes there. This component is owned by Oracle, so I'm not sure if it's something that fixed the issue or not, but regardless - we cannot repro this with the latest SSMA using ODP.NET stack.  
Regards,  

Alex.  
												
Hi Alex,  
I checked the "ssma_synchronization.xml" file. For Oracle, we used connection-provider value ="OleDB Provider" in the <servers> tag for the migration.  

I tested using the SSMA for Oracle GUI, which shows "ODP.Net" provider in the drop down, and it works correctly as you have mentioned.  

Our ODP.Net version is 12.2 (I think), since we only have Oracle 12.2 installed in our migration box.  
One more question, since we only use "SSMA for Oracle Console" for all our migrations.  

How do we use "ODP.Net" provider with SSMA for Oracle Console version 8.21?  

The sample file: "Sample Console Scripts\ServersConnectionFileSample.xml" in the installation directory of SSMA shows only two options for "connection-provider": "OracleClient" and "OleDB Provider".  
Thanks and regards,  

Homebrew9
The OracleClient should be the ODP.NET. We actually ship it with SSMA, so you don't need to install it separately. The binary that is in the SSMA folder will be used anyway.  
Regards,  

Alex.