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,795
  • 14
  • 40
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

2 Answers2

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,215
  • 12
  • 21
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
  • 2
  • 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