Mittwoch, 19. Dezember 2012

Random (type 4) Universally Unique IDentifier in Oracle

Oracle provides a function called sys_guid() to generate unique identifier. But this function produces different results on different systems. It is explained here. The following custom function generates a 16 byte RAW variable containing a random UUID. This is a type 4 UUID according to RFC 4122.
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 uses the package dbms_crypto to generate 16 random bytes and sets the high nibble of the 7th byte (starting with 1) to 0100b (see 4.1.3. in RFC 4122). This is done with the binary functions from the utl_raw package. It might be necessary to grant the execute permission for the dbms_crypto package:
grant execute on sys.dbms_crypto to random_uuid_user;
This is a result of a StackExchange question.