How to generate random unique UUID in Oracle?

June 26, 2019 No comments QA Oracle UUID

According to the rfc4122 specification, UUID is a 128 bits long identifier used to uniquely identify objects in various applications and systems. Many programming languages gives us ability to generate UUID using API, however Oracle don't provide a function which can do this directly. We need to use some other indirect solutions:


1. Using sys_guid function

Oracle provide a function called sys_guid() which generates unique identifiers and it can be used, with a simple modification, to generate UUIDs.

select SYS_GUID() from dual;

Result:

SYS_GUID
---------------------------------
7685AF6BCED84898803641EFB4F55B6D

Now we can can use regexp_replace and rawtohex function to generate random UUID. 

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 UUID from dual;

Result:

UUID
------------------------------------
B186D2A4-DF8A-4B86-8547-FCCA26719D78


2. Using Java based function

Another method to generate unique indentifiers in Oracle is to use Java based function that will generate UUIDs using java.util API.

create or replace and compile
java source named "RandomUUID"
as
public class FrontBackEndUtils {
   public static String randomUUID() {
        return java.util.UUID.randomUUID().toString();
   }
}

create or replace function RandomUUID
return varchar2
as language java
name 'FrontBackEndUtils.randomUUID() return java.lang.String';

select RandomUUID() from dual;

Result:

Java source RandomUUID compiled
Function RANDOMUUID compiled

RANDOMUUID()
------------------------------------
9167a85f-d317-422d-8e01-3cfdc949ee4a


3. Using dbms_crypto and utl_raw functions

create or replace function RandomUUID 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 RandomUUID;

To use dbms_crypto function you will need to grant special permissions to your database user:

grant execute on sys.dbms_crypto to uuid_user;
{{ message }}

{{ 'Comments are closed.' | trans }}