How should I store a GUID in Oracle?
As others have stated, there is a performance hit using GUIDs compared to numeric sequences. That said, there is a function named "SYS_GUID()" available since Oracle 8i that provides the raw equivalent:
SQL> SELECT SYS_GUID() FROM DUAL;SYS_GUID()--------------------------------248AACE7F7DE424E8B9E1F31A9F101D5
A function could be created to return a formatted GUID:
CREATE OR REPLACE FUNCTION GET_FORMATTED_GUID RETURN VARCHAR2 IS guid VARCHAR2(38) ;BEGIN SELECT SYS_GUID() INTO guid FROM DUAL ; guid := '{' || SUBSTR(guid, 1, 8) || '-' || SUBSTR(guid, 9, 4) || '-' || SUBSTR(guid, 13, 4) || '-' || SUBSTR(guid, 17, 4) || '-' || SUBSTR(guid, 21) || '}' ; RETURN guid ;END GET_FORMATTED_GUID ;/
Thus returning an interchangeable string:
SQL> SELECT GET_FORMATTED_GUID() FROM DUAL ;GET_FORMATTED_GUID()--------------------------------------{15417950-9197-4ADD-BD49-BA043F262180}
A note of caution should be made that some Oracle platforms return similar but still unique values of GUIDs as noted by Steven Feuerstein.
Update 11/3/2020: With 10g, Oracle added support for regular expression functions which means the concatenation can be simplified using the REGEXP_REPLACE()
function.
REGEXP_REPLACE( SYS_GUID(), '([0-9A-F]{8})([0-9A-F]{4})([0-9A-F]{4})([0-9A-F]{4})([0-9A-F]{12})', '{\1-\2-\3-\4-\5}')
The expression breaks out the string value returned by SYS_GUID()
into 5 groups of hexadecimal values and rebuilds it, inserting a "-" between each group.
If I understand the question properly, you want to generate a unique id when you insert a row in the db.
You could use a sequence to do this. link here
Once you have created your sequence you can use it like this:
INSERT INTO mytable (col1, col2) VALUES (myseq.NEXTVAL, 'some other data');