Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is Oracle's SYS_GUID() UUID RFC 4122 compliant?

Tags:

uuid

oracle

rfc

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 
like image 795
nulldevice Avatar asked Jun 28 '11 11:06

nulldevice


People also ask

What is Oracle UUID?

Oracle sequences are frequently used to provide unique numbers for primary keys where an appropriate unique key is not available. The use of sequences can cause a problem during data migrations and replication processes where duplication of the sequences occur.

How do I generate a random UUID in SQL?

The function random_uuid returns a randomly generated UUID, as a string of 36 characters. This function can be used to generate values for columns of type UUID in an INSERT or UPDATE SQL statements. In this example, a randomly generated UUID is fetched using the random_uuid function.

How do I find my Oracle GUID?

You can use the SYS_GUID() function to generate a GUID in your insert statement: insert into mytable (guid_col, data) values (sys_guid(), 'xxx'); The preferred datatype for storing GUIDs is RAW(16).


Video Answer


1 Answers

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   
like image 73
scottrudy Avatar answered Sep 22 '22 01:09

scottrudy