Search code examples
oracle-databaseuuid

How to generate a version 4 (random) UUID on Oracle?

This blog explains, that the output of sys_guid() is not random for every system:

http://feuerthoughts.blogspot.de/2006/02/watch-out-for-sequential-oracle-guids.html

Unfortunately I have to use such a system.

How to ensure to get a random UUID? Is it possible with sys_guid()? If not how to reliably get a random UUID on Oracle?

Solution

  • I use this now as a workaround:

    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;