Converting Raw(16) to GUID

Question

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

I need to find a simple and fast way to convert about 14 million items. I have exported the list to several delimited files and I am able to import this data into a schema.

Answer 1

A RAW(16) basically is a GUID: it's a 16-byte hex value. So one option is to just leave it alone. Oracle will implicitly cast between character and hex, so if you're looking for a row whose raw value is FE2BF132638011E3A647F0DEF1FEB9E8, you can use a string in your query:

SELECT *
FROM myTable
WHERE myRaw16Column = 'FE2BF132638011E3A647F0DEF1FEB9E8';

If you want to change the 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 CHAR(36) dash-formatted GUID, things get complicated quickly:

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