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

Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...