How to Generate a UUID in Oracle
The simplest way to generate a UUID in Oracle is the built-in SYS_GUID() function, which returns a 16-byte globally unique identifier with no extension or setup required. For embedded database UUID generation, the sqlite uuid guide covers both server and mobile app scenarios.
sqlplus
-- Generate a GUID directly in a query
SELECT SYS_GUID() FROM DUAL;
-- Output: 5A3F8B2C1D4E4F6A9B8C7D6E5F4A3B2C (RAW hex, no hyphens)
-- Use it as a column default
CREATE TABLE users (
id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
email VARCHAR2(255) NOT NULL
);
-- Insert without specifying id -- generated automatically
INSERT INTO users (email) VALUES ('user@example.com');Explanation
SYS_GUID()can be called directly in aSELECT ... FROM DUALfor a one-off value, or set as a columnDEFAULTso everyINSERTgets a fresh identifier automatically.- The result is a
RAW(16)value displayed as 32 hex characters with no hyphens — it will not look like a typical550e8400-e29b-41d4-a716-446655440000UUID string until you format it. SYS_GUID()uses Oracle's own GUID generation algorithm, not a standard UUID version — it isn't guaranteed to follow the RFC 4122 versioning and variant bits that v4 UUIDs from other systems carry.- Because of that, don't assume a value from
SYS_GUID()will parse as a valid UUID in application code that enforces RFC 4122 formatting — treat it as an Oracle-native identifier instead.
Convert a String to a UUID in Oracle
A UUID generated elsewhere — in application code or another database — arrives as a standard hyphenated string, and Oracle needs it converted before it fits into a RAW(16) column. Use the mysql uuid guide to optimize UUID index performance — BINARY(16) uses half the space of VARCHAR(36).
sqlplus
-- Convert a hyphenated UUID string to RAW(16) for storage
SELECT HEXTORAW(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', ''))
FROM DUAL;
-- Convert a RAW(16) value back to a plain hex string
SELECT RAWTOHEX(id) FROM users WHERE email = 'user@example.com';
-- Simpler alternative: store as VARCHAR2(36) if the hyphenated
-- display format matters more than the 16-byte storage savings
CREATE TABLE sessions (
id VARCHAR2(36) PRIMARY KEY,
user_id RAW(16) NOT NULL
);
-- Insert a UUID generated in application code directly -- no conversion needed
INSERT INTO sessions (id, user_id)
VALUES ('550e8400-e29b-41d4-a716-446655440000', SYS_GUID());Explanation
HEXTORAW(REPLACE(uuid_string, '-', ''))strips the hyphens and converts the remaining 32 hex characters into aRAW(16)value for storage.RAWTOHEX()reverses that, but returns a plain hex string with no hyphens — reinsert them manually withSUBSTR()if you need the display format back.RAW(16)is the storage-efficient choice: half the size ofVARCHAR2(36)and faster index comparisons, but every read and write needs a conversion step.VARCHAR2(36)needs no conversion at all and is human-readable in query results and logs — a real trade-off against the smaller footprint ofRAW(16), not a strictly worse option.
Other Options for Generating UUIDs in Oracle
When an application needs a standards-compliant UUID rather than Oracle's native GUID format — for compatibility with other databases or services — the practical approach is to generate it outside SYS_GUID() entirely.
sqlplus
-- Format one SYS_GUID() call as a hyphenated string for display only
-- (still Oracle's native GUID -- not an RFC 4122 v4 UUID)
SELECT LOWER(
SUBSTR(hex, 1, 8) || '-' || SUBSTR(hex, 9, 4) || '-' ||
SUBSTR(hex, 13, 4) || '-' || SUBSTR(hex, 17, 4) || '-' ||
SUBSTR(hex, 21, 12)
) AS display_uuid
FROM (SELECT RAWTOHEX(SYS_GUID()) AS hex FROM DUAL);
sqlplus
-- Practical alternative: generate the UUID in application code
-- (Java's UUID.randomUUID(), Python's uuid.uuid4(), etc.) and
-- insert it as-is -- Oracle just stores the value you provide.
INSERT INTO orders (id, order_date)
VALUES ('550e8400-e29b-41d4-a716-446655440000', SYSDATE);
-- id can be RAW(16) (convert first with HEXTORAW/REPLACE)
-- or VARCHAR2(36) (insert the hyphenated string directly)Explanation
- Capturing
SYS_GUID()once in a subquery before formatting avoids the bug of calling it twice and getting two different values back. - The formatted output above is still Oracle's proprietary GUID algorithm dressed up with hyphens — it does not become an RFC 4122 UUID just by looking like one.
- In Oracle 18c+ and 23ai environments, teams that need standards-compliant UUIDs typically generate them in application code and let Oracle store the value as-is.
- Choose
RAW(16)when storage efficiency matters, converting withHEXTORAW/REPLACE, orVARCHAR2(36)to insert the hyphenated string directly with no conversion.