Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

There are different approaches to inserting a UUID into an Oracle Database table, ranging from Java to various ways of doing it in PL/SQL (as described here). Not many of these seemed appealing to me so I came up with my own approach using a trigger.

There was a bug in earlier versions of Oracle on AIX that caused SYS_GUID() to generate non-unique IDs so if you're still on an older version and using AIX, check this Oracle Support Note first: DUPLICATE SYS_GUID generated on AIX can lead to ORA-1 errors during enqueue (Doc ID 1371805.1).
sqldev_ddl.png


Ok so triggers are a little bit like PL/SQL, but you don't have to worry about calling them, just create the trigger on a table and you're done. In my example below I assume that there is a table with the name MY_TABLE which has a column named MY_UUID of type VARCHAR2(36) that will hold the UUID.

Here's the code for the trigger...
 UUID Trigger
CREATE OR REPLACE TRIGGER MY_INSERT_UUID_TRIGGER
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
DECLARE
l_sysguid varchar2(32);
BEGIN
SELECT LOWER(SYS_GUID()) into l_sysguid FROM dual;
SELECT
substr(l_sysguid, 1, 8) || '-' ||
substr(l_sysguid, 9, 4) || '-' ||
substr(l_sysguid, 10, 4) || '-' ||
substr(l_sysguid, 15, 4) || '-' ||
substr(l_sysguid, 20, 12)
INTO :new.MY_UUID FROM dual;
END;




This trigger is created so that it adds the UUID value on each insert. A local variable, l_sysguid, is used to hold the output of the SYS_GUID() call, then SUBSTR() is used to split up the value into separate parts as required for a UUID and append them with dashes.

The output of SYS_GUID() looks something like this...3F0900B5E3783798F2E0302D7120A3DF. That is a unique value, but not necessarily a RFC 4122 type UUID, but it doesn't need to be. The logic in the trigger converts all the hexadecimal letters to lower case and then creates a value would look something like...3f0900b5-e378-3798-f2e0-302d7120a3df.

Pretty simple and it works well.

-i

Please leave your comments or feedback below!
comments powered by Disqus
Other posts you may like...
Hi! You can search my blog here ⤵
Or browse the recent top tags...

Recent Blog Posts

Using DeoxIT to repair old game catridges

WebLogic Maven Plugin - How to fix the MojoExecutionException: The artifact location was not specified

jPhotoFrame version 0.4 released with a whole new layout engine

Upcycling a couple of old broken lamps to create something amazing

A custom exception mapper and writer for a RESTful JAX-RS Jersey service

How to fix Plex error - Sorry there was a problem playing this item

Jersey JAX-RS filters and interceptors execution order for a POST request

Fix your Mac - users not showing on the macOS login screen when FileVault is enabled

BMB-012 Nanoblock T-Rex Skeleton Model assembly

Writing a custom MessageBodyReader to process POST body data with Jersey

Recent Galleries

BMB-012 Nanoblock T-Rex Skeleton Model assembly

Tiny Arcade revision 6 kit assembly and decal application

Atari Lynx repair - Part 5 - McWill LED screen mod installation

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 2 - re-capping the motherboard

Atari Lynx repair - Part 3 - broken speaker replacement

Atari Lynx repair - Part 1 - introduction and case disassembly

Building a custom Atari Lynx game box storage shelf unit in a day

Protecting old Atari Lynx game boxes with snug fit plastic sleeves

Monument Valley 2 is released and does not disappoint

Blogs and Friends

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity