[PL/SQL] How to generate UUID

Quote from lukasz.ciesla on October 15, 2019, 8:06 pm

The UUID (Universally Unique Identifier) is a 128-bit number represented as five sequences of hex digits separated by dashes. It may be used to identify some information like message, record or object identifier.

Below is an example of UUID. More about UUID and it's history you can find here.

6d4ab1e2-d3f9-4eae-e050-11ac020000a3

1. How to implement UUID in PL/SQL

Oracle Database hasn't implemented UUID function yet, but there is a similar function called sys_guid(). The functions returns GUID which is a 128-bit number represented as sequence of hex digits without dashes. The idea to make UUID from GUID is very simple - you have to just insert dashes into right places. Below is an example of function which converts sys_guid() to UUID.

[adinserter block="1"]

function generate_uuid return varchar2 is
    v_uuid varchar2(36);
    v_guid varchar2(32);
begin
    v_guid := sys_guid();
    v_uuid := lower(
                substr(v_guid, 1,8) || '-' || 
                substr(v_guid, 9,4) || '-' || 
                substr(v_guid, 13,4) || '-' || 
                substr(v_guid, 17,4) || '-' || 
                substr(v_guid, 21)
                );
    return v_uuid;
end generate_uuid;