How to generate a GUID in Oracle?

You can use the SYS_GUID() function to generate a GUID in your insert statement:

insert into mytable (guid_col, data) values (sys_guid(), 'xxx');

The preferred datatype for storing GUIDs is RAW(16).

As Gopinath answer:

 select sys_guid() from dual
 union all
 select sys_guid() from dual
 union all 
 select sys_guid() from dual

You get

88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601

As Tony Andrews says, differs only at one character

88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601

Maybe useful: http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html

Example found on: http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm

SELECT REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5') MSSQL_GUID  FROM DUAL

Result:

6C7C9A50-3514-4E77-E053-B30210AC1082

You can also include the guid in the create statement of the table as default, for example:

create table t_sysguid
( id     raw(16) default sys_guid() primary key
, filler varchar2(1000)
)
/

See here: http://rwijk.blogspot.com/2009/12/sysguid.html

Tags:

Oracle

Guid

Related