10

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.

user3114639
  • 1,895
  • 16
  • 42
Mr.Brown
  • 103
  • 1
  • 1
  • 5
  • have you think about to convert the RAW GUID to varchar directly in your sql? https://community.oracle.com/thread/1063096?tstart=0 – Aris2World Jun 07 '16 at 16:42
  • This might help: https://stackoverflow.com/questions/18986712/creating-a-uuid-from-a-string-with-no-dashes – sebenalern Jun 07 '16 at 16:45

3 Answers3

17

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?

Community
  • 1
  • 1
Aris2World
  • 1,214
  • 12
  • 22
2

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

schwendler
  • 66
  • 3
  • Is there a way to reverse this function with Java ? see https://stackoverflow.com/questions/68895766/how-to-insert-uuid-into-raw16-column-with-jdbc-template – mattsmith5 Aug 23 '21 at 21:52
  • I just gave points to your questions, see the bounty to this questions – mattsmith5 Aug 23 '21 at 21:56
0

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);
TianCaiBenBen
  • 691
  • 5
  • 2