Oracle with GUID
Have you ever wanted to know that how Oracle generate GUID?
This is the answer. I found this website shows how to use Oracle to generate GUID for us.
http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html
This is the code
IS
SUBTYPE guid_t IS RAW (16);
SUBTYPE formatted_guid_t IS VARCHAR2 (38);
– Example: {34DC3EA7-21E4-4C8A-BAA1-7C2F21911524}
c_mask CONSTANT formatted_guid_t
:= ‘{________-____-____-____-____________}’;
FUNCTION is_formatted_guid (string_in IN VARCHAR2)
RETURN BOOLEAN;
FUNCTION formatted_guid (guid_in IN VARCHAR2)
RETURN formatted_guid_t;
FUNCTION formatted_guid
RETURN formatted_guid_t;
END guid_pkg;
/
CREATE OR REPLACE PACKAGE BODY guid_pkg
IS
FUNCTION is_formatted_guid (string_in IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
RETURN string_in LIKE c_mask;
END is_formatted_guid;
FUNCTION formatted_guid (guid_in IN VARCHAR2)
RETURN formatted_guid_t
IS
BEGIN
– If not already in the 8-4-4-4-rest format, then make it so.
IF is_formatted_guid (guid_in)
THEN
RETURN guid_in;
– Is it only missing those squiggly brackets?
ELSIF is_formatted_guid (‘{‘ || guid_in || ‘}’)
THEN
RETURN formatted_guid (‘{‘ || guid_in || ‘}’);
ELSE
RETURN ‘{‘
|| SUBSTR (guid_in, 1, 8 )
|| ‘-’
|| SUBSTR (guid_in, 9, 4)
|| ‘-’
|| SUBSTR (guid_in, 13, 4)
|| ‘-’
|| SUBSTR (guid_in, 17, 4)
|| ‘-’
|| SUBSTR (guid_in, 21)
|| ‘}’;
END IF;
END formatted_guid;
FUNCTION formatted_guid
RETURN formatted_guid_t
IS
BEGIN
RETURN formatted_guid (SYS_GUID);
END formatted_guid;
END guid_pkg;
/
