1

I have dealt with the problem that I need to convert data from the Oracle database to the SQL Server database and there is no way except SQL Server Migration Assistant.

In SSMA there is a TypeMapping which allows you to define types you want to cast. I added Raw[16](Guid in Oracle) to UniqueIdentifier(Guid in oracle) as TypeMapping.

But when SSMA started to convert data it returns this exception:

The given value of type Byte[] from the data source cannot be converted to type uniqueidentifier of the specified target column.

Sasan
  • 644
  • 9
  • 29

3 Answers3

2

ORACLE SYS_GUID is RAW(16) and it is 32 character hexadecimal representation.

The equivalent, SQL Server datatype Uniqueidentifier, is 16 byte binary value, which is 36 character representation. SQL Server UniqueIdentifier

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value

You can think of below options:

  • I would suggest you to use VARCHAR(32) to represent corresponding ORACLE GUID in SQLServer in the typemapping.

  • you can have a default value for NEWID() for the target column, values will get assigned when you load the data

  • Have the target datatype as VARCHAR(36) and once you are done with migration, you can start using NEWID() for future values. As GUIDs are going to be unique, you will not face issues.
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
1
DECLARE @uniqORAconvMS varchar(32) = '5cf5d1b5db12d38067affb261d9619dc'

SELECT left(@uniqORAconvMS,8) + '-' + SUBSTRING(@uniqORAconvMS,9,4) + '-' + SUBSTRING(@uniqORAconvMS,13,4) + '-' + SUBSTRING(@uniqORAconvMS,17,4)  + '-' + RIGHT(@uniqORAconvMS,12)

-- xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
-- 5cf5d1b5-db12-d380-67af-fb261d9619dc

-- With TRY_CONVERT to check valid uniqueidentifier
SELECT TRY_CONVERT (UNIQUEIDENTIFIER, LEFT(@uniqORAconvMS,8) + '-' + SUBSTRING(@uniqORAconvMS,9,4) + '-' + SUBSTRING(@uniqORAconvMS,13,4) + '-' + SUBSTRING(@uniqORAconvMS,17,4) + '-' + RIGHT(@uniqORAconvMS,12))
Martin Bárta
  • 11
  • 2
  • 7
    Please [edit](https://stackoverflow.com/posts/58412202/edit) your post. Don't post on comment section. – 4b0 Oct 16 '19 at 11:37
  • This was helpful to me because we were doing a similar operation but going from SQL Server to Oracle and the addition of the hyphens above clued me in that I need to replace/remove the hypens in the string beforehand for the value to insert correctly into Oracle RAW(16) using HEXTORAW(). gracias! – Jeff Mergler Apr 15 '20 at 15:58
1

The answer by Martin is slightly incorrect. If you use entity framework, you'll notice the GUIDs returned from oracle aren't just split up by dashes, but also reordered. So 5cf5d1b5db12d38067affb261d9619dc should become b5d1f55c-12db-80d3-67af-fb261d9619dc, not 5cf5d1b5-db12-d380-67af-fb261d9619dc.

DECLARE @uniqORAconvMS varchar(32) = '5cf5d1b5db12d38067affb261d9619dc';

SELECT SUBSTRING(@uniqORAconvMS,7,2) + SUBSTRING(@uniqORAconvMS,5,2) + SUBSTRING(@uniqORAconvMS,3,2) + SUBSTRING(@uniqORAconvMS,1,2) + '-' + SUBSTRING(@uniqORAconvMS,11,2) + SUBSTRING(@uniqORAconvMS,9,2) + '-' + SUBSTRING(@uniqORAconvMS,15,2) + SUBSTRING(@uniqORAconvMS,13,2) + '-' + SUBSTRING(@uniqORAconvMS,17,4)  + '-' + RIGHT(@uniqORAconvMS,12);
Nielsvh
  • 1,151
  • 1
  • 18
  • 31