SSMA for Oracle behavior with string data containing ASCII NULL character

Question

Hi,

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"
SQL Server version: Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64) Nov 1 2020 00:13:28 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
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:

1>
2> select x, y, convert(varbinary(1000), y) as y_vb from dbo.T_NULL;
3> go
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)
1>
2>

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
1>
2> select x, y, convert(varbinary(1000), y) as y_vb from dbo.T_NULL;
3> go
x           y          y_vb
----------- ---------- ------------------------------------
          1 ABCDE      0x4142434445
          2 ABCDE      0x414243444500
          3 ABCDE      0x41424344450046
          4 ABCDE      0x414243444500464748
          5 ABCDE      0x41424344450046474849

(5 rows affected)
1>
2>

Is this a known bug in SSMA for Oracle 8.21?
Has it been fixed in any higher version?

Thanks and regards,
Homebrew9

Answer 1

Hey,

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.