Convert Raw (16) to GUID

Question

Is there a way to create a SQL script that converts an Oracle RAW(16) list to a GUID ?

I need to find an easy and quick way to convert about 14 million items. I have exported the list to several delimited files, and I can import this data into the schema.

Answer 1

A RAW(16) is basically a GUID: it is a hexadecimal hexadecimal value. Therefore, one option is to simply leave it alone. Oracle will implicitly cast between a character and a hexadecimal, so if you are looking for a string with the raw value FE2BF132638011E3A647F0DEF1FEB9E8 , you can use the string in the query:

 SELECT * FROM myTable WHERE myRaw16Column = 'FE2BF132638011E3A647F0DEF1FEB9E8'; 

If you want to change RAW(16) to CHAR(32) for your conversion, you can use RAWTOHEX as @tbone suggests.

 INSERT INTO NewTable (myGUIDColumn, ...) SELECT RAWTOHEX(myRawColumn), ... FROM OldTable 

If you want to make it a GUID CHAR(36) with a dash formatted, things get complicated quickly:

 INSERT INTO NewTable (myGUIDColumn, ...) SELECT REGEXP_REPLACE(myRaw16Column, '(.{8})(.{4})(.{4})(.{4})(.*)', '\1-\2-\3-\4-\5'), ... FROM OldTable