How to implement automatic generation of UUID in Oracle?

1 year ago
Emily Johnson
2 minutes

In Oracle database, you can automatically generate UUIDs by using the UUID function.

UUID stands for Universally Unique Identifier, which is a 128-bit number used to uniquely identify information in a distributed computing environment.

Here is an example of generating a UUID using the UUID function in Oracle:

SELECT SYS_GUID() AS UUID FROM DUAL;

This query will return a new UUID value.

Additionally, if it’s necessary to automatically generate a UUID when inserting data, it can be achieved using default values and triggers. Start by creating a field in the table to store the UUID, and then use a trigger to automatically populate that field when inserting data.

Here is an example:

Firstly, create a table:

CREATE TABLE my_table (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    name VARCHAR2(50)
);

Next, establish a trigger.

CREATE OR REPLACE TRIGGER my_table_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    :NEW.id := SYS_GUID();
END;
/

Now, when you insert data into this table, the id field will be automatically filled with a UUID value.

INSERT INTO my_table (name) VALUES ('John');

Please note that in Oracle, UUIDs are stored as RAW data type rather than string type. If you need to output UUID as a string, you can use the UTL_RAW.CAST_TO_VARCHAR2 function for conversion.

SELECT UTL_RAW.CAST_TO_VARCHAR2(id) AS UUID FROM my_table;

The above is a method for generating UUID automatically in Oracle.

bannerAds