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.