[PL/SQL] How to generate UUID

[PL/SQL] How to generate UUID

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.


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.

function generate_uuid return varchar2 is
    v_uuid varchar2(36);
    v_guid varchar2(32);
    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;
If the article was helpful to you, you can support me by:

Leave a Reply

1 Comment threads
0 Thread replies
Most reacted comment
Hottest comment thread
1 Comment authors
JMJMJM Recent comment authors
newest oldest most voted
Notify of

I couldn’t help but commenting.

You do realize that sys_guid() actually returns a UUID as it is… the Term GUID only refers to certain implementations of the UUID standard, probably most commonly the Microsoft implementation…

So your generate_uuid function doesn’t actually generate a UUID, but instead generates a (common) textual representation of one which is only really useful for printing/displaying it and not storage it.

(Or more precisely formulated, it Generates a UUID by calling the sys_guid() function and then turns that that UUID in its canonical textual representation)

Close Menu