oracle_sys_guid_to_number_and_back.sql

-- generate a GUID in Oracle

select sys_guid()
from dual;

-- 50C2D6992A3481B5E0532637548C8476

-- convert it to a number
-- see https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7041297073738

select to_number('50C2D6992A3481B5E0532637548C8476', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
from dual;

-- 107349897823974919627571584270129202294

-- convert it back from a number to hex

select to_char(107349897823974919627571584270129202294, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
from dual;

-- 50C2D6992A3481B5E0532637548C8476

-- format GUID with dashes
-- see https://stackoverflow.com/questions/21101560/converting-raw16-to-guid
select lower(regexp_replace('50C2D6992A3481B5E0532637548C8476', '(.{8})(.{4})(.{4})(.{4})(.*)', '\1-\2-\3-\4-\5')) as formatted_guid
from dual;

-- 50c2d699-2a34-81b5-e053-2637548c8476