CODEWITHSUNDEEP
javaoracle-database
Mr.Brown
Mr.Brown

Reputation: 113

ORACLE RAW in string format to Standard GUID

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.

Upvotes: 11

Views: 20075

Answers (3)

TianCaiBenBen
TianCaiBenBen

Reputation: 711

SQL version

1. GUID to OracleRaw

-- Input GUID: B8A2B29B-87DF-B047-982F-2F1702E1D18B

-- Output OracleRaw: 9BB2A2B8DF8747B0982F2F1702E1D18B

SELECT hextoraw(substr(guid, 7, 2) || substr(guid, 5, 2) ||
            substr(guid, 3, 2) || substr(guid, 1, 2) ||
            substr(guid, 12, 2) || substr(guid, 10, 2) ||
            substr(guid, 17, 2) || substr(guid, 15, 2) ||
            substr(guid, 20, 4) || substr(guid, 25, 12))
FROM (SELECT 'B8A2B29B-87DF-B047-982F-2F1702E1D18B' guid FROM dual);

2. OracleRaw to GUID

-- Input OracleRaw: 9BB2A2B8DF8747B0982F2F1702E1D18B

-- Output GUID: B8A2B29B-87DF-B047-982F-2F1702E1D18B

SELECT UPPER(substr(hextoraw(oracleRaw), 7, 2) || substr(hextoraw(oracleRaw), 5, 2) || 
         substr(hextoraw(oracleRaw), 3, 2) || substr(hextoraw(oracleRaw), 1, 2) || '-' || 
         substr(hextoraw(oracleRaw), 11, 2)|| substr(hextoraw(oracleRaw), 9, 2) || '-' || 
         substr(hextoraw(oracleRaw), 15, 2)|| substr(hextoraw(oracleRaw), 13, 2)|| '-' || 
         substr(hextoraw(oracleRaw), 17, 4)|| '-'|| substr(hextoraw(oracleRaw), 21, 12))
FROM  (SELECT '9BB2A2B8DF8747B0982F2F1702E1D18B' oracleRaw FROM dual);

Upvotes: 1

schwendler
schwendler

Reputation: 66

I wrote a console app to do this conversion. It's useful because I need to do this many times a day.

It's necessary compile and define the output directory on path.

https://github.com/lucassc/oracleuuid.

Exemple

Upvotes: 2

Aris2World
Aris2World

Reputation: 1234

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?

Upvotes: 18

Related Questions