sql – How to get version 4 (random) UUID?

Question:

This blog explains that the values ​​returned by sys_guid() function are not actually random on some systems.

Unfortunately, I work on such a system.

How to get a random UUID?
Is it possible with sys_guid() ? If not, how do I get a reliably random UUID?

Free translation How to generate a version 4 (random) UUID on Oracle? from @ceving

Helpful comments:

In general, UUIDs are not truly random. – @jpaugh

By "random UUID" I meant "type 4 UUID". I think everyone here understands what pseudo-randomness means . – @ceving

Answer:

Here is a fully working example (based on @Pablo Santa Cruz 's answer):

create or replace and compile java source named "RandomUUID" as
public class RandomUUID {
    public static String create() {
        return java.util.UUID.randomUUID().toString();
    }
}
/
create or replace function RandomUUID return varchar2 as 
    language java name 'RandomUUID.create() return java.lang.String';
/

SQL> select randomUUID() uuid from dual;

UUID
--------------------------------------------------------------------------------
f83bf110-96e7-45d2-9abc-8ebabde8ee5f

But it's better to SYS_GUID if possible.
Check ID 1371805.1 on MOS (Editor's note: requires registration) – this bug has been fixed in version 11.2.0.3.

Addition

Which of the options – Java or PL / SQL – is more productive depends on how the function is used.
Note. ed .: I omitted commenting on the performance comparison results, because. are no longer true.
Compare with this example:

create table t1 as
    select level n from dual connect by level <= 10e5;

declare
    t number;
    c number;
    rawval raw (16);
    chrval varchar2 (36);
begin 
    t := dbms_utility.get_time;
    select count(*) into c from t1 where to_char(a) > RandomUUID ();
    dbms_output.put_line ('SQL/JAVA   elapsed '||(dbms_utility.get_time-t)/100);
    t := dbms_utility.get_time;
    select count(*) into c from t1 where to_char(a) > random_uuid ();
    dbms_output.put_line ('SQL/PLSQL  elapsed '||(dbms_utility.get_time-t)/100);
    t := dbms_utility.get_time;
    for i in 1..10e5 loop chrval := RandomUUID; end loop;
    dbms_output.put_line ('PLSQL/JAVA  elapsed '||(dbms_utility.get_time-t)/100);
    t := dbms_utility.get_time;
    for i in 1..10e5 loop rawval := random_uuid; end loop;
    dbms_output.put_line ('PLSQL/PLSQL elapsed '||(dbms_utility.get_time-t)/100);
end;
/
SQL/JAVA    elapsed ,01
SQL/PLSQL   elapsed ,01
PLSQL/JAVA  elapsed 50,67
PLSQL/PLSQL elapsed 40,21

In version 4, GUID values ​​are not completely random. Some bytes have fixed values. Not sure why they did this, or if it even matters, but according to Generate a UUID compliant with RFC 4122 :

The procedure to generate a version 4 UUID is as follows:

Generate 16 random bytes (=128 bits)
Adjust certain bits according to RFC 4122 section 4.4 as follows:
    set the four most significant bits of the 7th byte to 0100'B, so the high nibble is "4"
    set the two most significant bits of the 9th byte to 10'B, so the high nibble will be one of "8", "9", "A", or "B".
Encode the adjusted bytes as 32 hexadecimal digits
Add four hyphen "-" characters to obtain blocks of 8, 4, 4, 4 and 12 hex digits
Output the resulting 36-character string "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"

The values ​​that a Java function generates appear to conform to the standard.

Loose translation of answer from member @Jon Heller

Post Views: 20
Scroll to Top