UUIDs are the standard way to identify things across systems: REST resources, messages between services, records created on mobile devices. Oracle AI Database 26ai adds four functions for them. UUID generates a random version 4 UUID, RAW_TO_UUID and UUID_TO_RAW convert between the 16-byte RAW form and the familiar text form with hyphens, and IS_UUID validates a string.
Code for This Guide
The main example is in the examples/misc-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
uuid([version]) -- returns RAW(16) raw_to_uuid(raw) -- returns VARCHAR2, with hyphens uuid_to_raw(varchar2) -- returns RAW(16) is_uuid(varchar2) -- returns BOOLEAN
The optional version argument of UUID can only be 0 or 4; both give a random version 4 UUID. IS_UUID accepts UUIDs with or without hyphens, in either case.
Generate, Format, and Validate
The first query generates two UUIDs and shows one as RAW and one as text. The second validates three strings, and the third converts a text UUID back to RAW.
Example:
select uuid() as uuid_raw,
raw_to_uuid(uuid()) as uuid_text
from dual;
select v, is_uuid(v) as valid
from (values ('123e4567-e89b-42d3-a456-426614174000'),
('123E4567E89B42D3A456426614174000'), ('not-a-uuid')) t (v);
select uuid_to_raw('123e4567-e89b-42d3-a456-426614174000') as as_raw from dual;Output:
UUID_RAW UUID_TEXT ___________________________________ _______________________________________ 2AC125AA577C4335866D6FC3BF6D8D75 0e4521b5-f12e-4621-8f42-289eec35bf6e V VALID _______________________________________ ________ 123e4567-e89b-42d3-a456-426614174000 true 123E4567E89B42D3A456426614174000 true not-a-uuid false AS_RAW ___________________________________ 123E4567E89B42D3A456426614174000
Each call returns a new random value, so your UUIDs will differ. In the text form, the 4 at the start of the third group marks version 4.
Store UUIDs
Store UUIDs as RAW(16), which takes 16 bytes, rather than as 36-character text. Convert at the edges: RAW_TO_UUID when returning a value to an application, UUID_TO_RAW when receiving one. A column default works as for any function:
Example:
create table api_tokens ( token_id raw(16) default uuid() primary key, issued timestamp default systimestamp ); select raw_to_uuid(token_id) as token_id from api_tokens;
Use IS_UUID to validate input before converting it, for example in a CHECK constraint or in WHERE, so that a bad string is rejected with a clear message instead of a conversion error.
UUID Compared with SYS_GUID
SYS_GUID values are generated from a counter and come out nearly sequential, which indexes well but makes them predictable. UUID values are random, so they are safe to expose in URLs and cannot be guessed from one another, at the cost of spreading inserts across the whole index.
Related Guides
Conclusion
UUID generates random version 4 UUIDs as RAW(16), RAW_TO_UUID and UUID_TO_RAW convert between RAW and the hyphenated text form, and IS_UUID validates strings. Store the RAW form, convert at the application boundary, and prefer UUID over SYS_GUID when identifiers must not be guessable.
