ORACLE RAW in string format to Standard GUID

Question

I am trying to look for a solution to convert Oracle RAW GUID in String format to a standard GUID format. I am unable to find a solution for my use case. Here is an example of what I am looking for:

ORACLE RAW (String): 9BB2A2B8DF8747B0982F2F1702E1D18B 

This needs to be converted using Java code into standard or bracketed GUID which is

B8A2B29B-87DF-B047-982F-2F1702E1D18B or {B8A2B29B-87DF-B047-982F-2F1702E1D18B} 

Thanks for your help in advance.

Answer 1

A simple way is to convert the RAW GUID to VARCHAR when you select it. Then read it from result set as a String. This is the formula:

select 
 upper(
    regexp_replace(
        regexp_replace(
            hextoraw('9BB2A2B8DF8747B0982F2F1702E1D18B'),
            '(.{8})(.{4})(.{4})(.{4})(.{12})',
            '\1-\2-\3-\4-\5'
        ),
        '(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})',
        '\4\3\2\1-\6\5-\8\7\9'
    )
 ) from dual

This is the reference where I've found the query (I have to adjust it because the original has some errors): https://community.oracle.com/thread/1063096?tstart=0.

Or if you want to do it with Java then to translate the above solution in Java is quite simple:

/**
 * input: "9BB2A2B8DF8747B0982F2F1702E1D18B"
 * output: "B8A2B29B-87DF-B047-982F-2F1702E1D18B";
 */
public String hexToStr(String guid) {       
    return guid.replaceAll("(.{8})(.{4})(.{4})(.{4})(.{12})", "$1-$2-$3-$4-$5").replaceAll("(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})", "$4$3$2$1-$6$5-$8$7$9");
}

A more standard way using the class java.util.UUID in not possible because Oracle implementation of SYS_GUID() is not compliant with RFC 4122. See Is Oracle's SYS_GUID() UUID RFC 4122 compliant?