32

I wonder if Oracle's SYS_GUID() function returns a RFC 4122 compliant UUID. For example:

SQL> select sys_guid() from dual;

SYS_GUID()
--------------------------------
A6C1BD5167C366C6E04400144FD25BA0

I know, that SYS_GUID() returns a 16 byte RAW datatype. Oracle uses RAWTOHEX() and probably TO_CHAR() to print out the above ID. Is it correct to interpret this as a UUID compliant string format like:

A6C1BD51-67C3-66C6-E044-00144FD25BA0

I think it's not compliant to the RFC 4122 standard, because the definition says, that a valid UUID must name the UUID-Version within the UUID itself.

Syntax for a RFC 4122 compliant UUID (Version 3):

xxxxxxxx-xxxx-3xxx-xxxx-xxxxxxxxxxxx
APC
  • 144,005
  • 19
  • 170
  • 281
nulldevice
  • 323
  • 1
  • 3
  • 5

4 Answers4

27

If you want that format try this:

select regexp_replace(rawtohex(sys_guid())
       , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
       , '\1-\2-\3-\4-\5') 
         as FORMATTED_GUID 
 from dual

Example Results:

 FORMATTED_GUID                                                                  
 ------------------------------------
 F680233E-0FDD-00C4-E043-0A4059C654C9
Andrew Russell
  • 476
  • 6
  • 15
scottrudy
  • 1,633
  • 1
  • 14
  • 24
  • 2
    For a bulk data processing to get a bit better performance by avoiding REGEXP see [here](http://stackoverflow.com/questions/36354555/formating-string-without-regexp-replace-and-pl-sql) – Marmite Bomber Apr 05 '16 at 08:21
  • 1
    Thats not returning an valid version 3 UUID because it should have the format xxxxxxxx-xxxx-3xxx-xxxx-xxxxxxxxxxxx (version 3) or xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx (version 4) – ZerOne Jul 17 '18 at 09:17
25

SYS_GUID is Oracle's equivalent of UUID. It is globally unique. However, it is not compliant to RFC 4122; I'm inferring lack of compliance from the absence of references to UUID in the documentation (outside the Java XML documentation).

I suspect Oracle haven't natively implemented RFC 4122 because they don't think it scales. I can't imagine why else they would invent their own thing instead of complying to a standard.

APC
  • 144,005
  • 19
  • 170
  • 281
4

With sufficient privileges, it is possible to have Oracle generate compliant UUIDs.

1. By defining a SQL function

From https://stackoverflow.com/a/13956771, you can do the following: 

create or replace function random_uuid return RAW is
  v_uuid RAW(16);
begin
  v_uuid := sys.dbms_crypto.randombytes(16);
  return (utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid, 7, 1), '0F'), '40'), v_uuid, 7));
end random_uuid;

The function requires dbms_crypto and utl_raw. Both require an execute grant.

grant execute on sys.dbms_crypto to uuid_user;

2. Using a Java procedure

To create a Java procedure for creating a compliant UUID, see https://stackoverflow.com/a/13951615.

Community
  • 1
  • 1
NealeU
  • 1,264
  • 11
  • 23
1

RFC 4122 § 3. Namespace Registration Template (Page 5)

Validation mechanism:
Apart from determining whether the timestamp portion of the UUID
is in the future and therefore not yet assignable, there is no
mechanism for determining whether a UUID is 'valid'.

It is the generation process that determines "compliance" with RFC 4122, the UUID itself is just a 128 bit IDentifier.

Therefore the answer is yes, why would it not be compliant? A UUID is just an 128 bit Universaly Unique IDentifier, the rest of the spec is just recommended ways to help you/Oracle generate identifiers that do not collide with other systems that generate U.U. IDentifiers. If you or Oracle do not want to follow their recommendations, they are free to do so. Regardless, the uuid you gave is "in compliance" with RFC 4122 as the variant field starts with the bit sequence 111 which is "Reserved for future definition.". The spec was written in the past and does not restrict who can specify a "future definition", it certainly does not prevent Oracle from defining their own variants... therefore it is "in compliance"... lol.

p.s. I love how the original authors anticipated your question and added sarcasm quotes around 'valid'.

figl
  • 55
  • 1
  • 5