Converting Raw(16) to GUID

guid, oracle, sql

Solution

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

Problem

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.

Original source