locked
SSMA for Oracle. RAW(16) to uniqueidentifier conversion RRS feed

  • Question

  • We need to migrate data from Oracle tables having RAW(16) columns into SQL Server tables with corresponding uniqueidentifier columns.

    SSMA 2008 migration reports an error in this case ("The given value of type Byte[] from the data source cannot be converted to type uniqueidentifier..."). I can change Oracle SELECT statement so that it can convert RAW(16) to something else, i.e. formatted varchar2(36) or anything else. But I still get the similar error (type String ... cannot be converted...). This looks quite strange because normally SqlServer can convert formatted string representation of GUID to uniqueidentifie.

    Any way the question is:

    Is it possible to cast Oracle type to another type that could be converter to uniqueidentifier?

    Is it possible to tune the conversion procedure somehow, i.e. change SqlServer INSERT statement for a particular table or do something like this?

    If it's not possible then can I solve the problem in another way?

    Permanent changes of the structure of DBs is not an option for us. I would like to keep it as it is (Oracle's RAW16 -> SQlServer's uniqueidentifier) because otherwise I will need to fix this after the autoconversion (currently we have many installations of both database types and these columns are used in FKs and so on...).

    • Moved by Peja Tao Monday, September 19, 2011 6:10 AM Migration issue (From:SQL Server Tools General)
    Friday, September 16, 2011 12:34 PM

Answers

  • This is what I do. I put the Guid data into varchar columns and then run a TSQL script to convert the data. It seems that this is the only way for me.

    I still think that this has to be corrected in ssma or perhaps there is already a way to do this there.


    /Vlad Kravchuk
    • Marked as answer by Peja Tao Monday, September 26, 2011 1:51 PM
    Wednesday, September 21, 2011 6:40 AM

All replies

  • I don't know if this is a help, but I did find this interesting fact.  The byte order in Oracle RAW is not the same as in Microsoft GUIDs.

    GUID - A6CEC596-7A3D-4730-BCE4-822BF7242867
    RAW  - 96C5CEA6 3D7A 3047 BCE4 822BF7242867  - spaces added to help comparison.

     

    Now, I do not know why exactly this would be unconvertable, but the RAW with or without spaces cannot be cast as a uniqueidentifier, but when I put the - in it works fine.

    select CAST ('A6CEC596-7A3D-4730-BCE4-822BF7242867' as uniqueidentifier) -- works
    select CAST ('96C5CEA6-3D7A-3047-BCE4-822BF7242867' as uniqueidentifier) -- works
    select CAST ('96C5CEA63D7A3047BCE4822BF7242867' as uniqueidentifier) -- fails
    

     

    For what it is worth,

    RLF

     

    Friday, September 16, 2011 4:11 PM
  • I don't know if this is a help, but I did find this interesting fact.

    Thanks for the answer.

    The byte order is not important for me because I do not need to keep exact values of GUIDs - there are no important external dependencies in this case but internally it should work as expected until all the GUIDs are changed in the same way.

    In SSMA it's possible to edit SELECT statement for a table and therefore I can write something like this:
    SELECT
    SUBSTR("UNIQUEID",  1, 8) || '-' || SUBSTR("UNIQUEID",  9, 4) || '-' || SUBSTR("UNIQUEID", 13, 4) || '-' || SUBSTR("UNIQUEID", 17, 4) ||
    '-' || SUBSTR("UNIQUEID", 21) AS "UNIQUEID",
    "KURS_ID"....
    FROM MYSCHEMA.MYTABLE  t;

    So theoretically this string UNIQUEID is acceptable for the corresponding SqlServer INSERT statement and it works if I do this manually. But it does not work in SSMA ...


    /Vlad Kravchuk
    Friday, September 16, 2011 4:34 PM
  • I am sorry, but I have never used SSMA.  Can you put a CAST or CONVERT statement into the process to explicitly?  I suspect not, but just asking.

    Another, more tedious way, is to use SSMA to put the data into a character string (if possible) in a staging table, then use TSQL to move the data into the destination table.

    However, staging tables also provide some insulation against limited tools by putting more control into the programmer's hands.

    RLF

    Friday, September 16, 2011 9:15 PM
  • This is what I do. I put the Guid data into varchar columns and then run a TSQL script to convert the data. It seems that this is the only way for me.

    I still think that this has to be corrected in ssma or perhaps there is already a way to do this there.


    /Vlad Kravchuk
    • Marked as answer by Peja Tao Monday, September 26, 2011 1:51 PM
    Wednesday, September 21, 2011 6:40 AM
  • This is what I do. I put the Guid data into varchar columns and then run a TSQL script to convert the data. It seems that this is the only way for me.

    I still think that this has to be corrected in ssma or perhaps there is already a way to do this there.


    /Vlad Kravchuk

    I couldn't figure out how to get SSMA to convert the RAW(16) field into Varchar - the type mapping only allows you to choose one of the binary types, and if you force change the target type into varchar it says conversion error.

    In the end I let it use the default destination type (varbinary(16)) and then I wrote SQL to convert it into the uniqueidentifier format by converting the binary into string (type 2 = remove the 0x from the start) and then padding it with "-" to make it convertable to the uniqueidentifier format.

    e.g. This SQL after the data has been copied from Oracle into a varbinary field called 'APPLICATIONID':

    SELECT convert(uniqueidentifier,SUBSTRING(convert(varchar(max),[APPLICATIONID],2), 1, 8) + '-' + SUBSTRING(convert(varchar(max),[APPLICATIONID],2), 9, 4) + '-' + SUBSTRING(convert(varchar(max),[APPLICATIONID],2), 13, 4) + '-' + SUBSTRING(convert(varchar(max),[APPLICATIONID],2), 17, 4) + '-' + SUBSTRING(convert(varchar(max),[APPLICATIONID],2), 21, 12))

    Thursday, October 4, 2018 3:00 PM