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

oracle, uuid

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;

Problem

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?

Original source