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

Coherence and weblogic.xml in different types of J2EE web app deployments

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

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 3 - broken speaker replacement

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

Atari Lynx repair - Part 1 - introduction and case disassembly

jPhotoFrame updated to version 0.3.1 with an image rotation correction utility

iOS 11 pre-GM mini review before it gets revealed next week

Why you should never use Java enums as keys with Oracle Coherence caches

Hacking the VideoPak 7inch promotional brochure to work as a digital photo frame

Recent Galleries

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

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

Blogs and Friends

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

Blog Activity

Blog Activity