How to generate GUID in Oracle?

Question

Is it possible to auto-generate a GUID in an Insert statement?

Also, what type of field should I use to store this GUID?

Answer 1

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

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

The preferred data type for storing GUIDs is RAW (16).

As the answer of Gopinath:

  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 in only one character

88FDC68C75D D F955E040449808B55601
88FDC68C75D E F955E040449808B55601
88FDC68C75D F F955E040449808B55601

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

Answer 2

You can also include the directive in the create statement of the default table, 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

Answer 3

It's not clear what you mean by the guid auto-generator in the insert statement, but suppose I'm trying to do something like the following:

 INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Adams'); INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Baker'); 

In this case, I believe that the ID column should be declared as RAW (16);

I do it from the head. I do not have an instance of Oracle that can be tested against, but I think this is what you want.

Answer 4

sys_guid () is a bad option that other answers talked about. One way to generate UUIDs and prevent sequential values ​​is to generate random hexadecimal strings:

 select regexp_replace( to_char( DBMS_RANDOM.value(0, power(2, 128)-1), 'FM0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})', '\1-\2-\3-\4-\5') from DUAL; 

Answer 5

You can run the following query

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

Answer 6

you can use the function below to create your uuid

 create or replace FUNCTION RANDOM_GUID RETURN VARCHAR2 IS RNG NUMBER; N BINARY_INTEGER; CCS VARCHAR2 (128); XSTR VARCHAR2 (4000) := NULL; BEGIN CCS := '0123456789' || 'ABCDEF'; RNG := 15; FOR I IN 1 .. 32 LOOP N := TRUNC (RNG * DBMS_RANDOM.VALUE) + 1; XSTR := XSTR || SUBSTR (CCS, N, 1); END LOOP; RETURN SUBSTR(XSTR, 1, 4) || '-' || SUBSTR(XSTR, 5, 4) || '-' || SUBSTR(XSTR, 9, 4) || '-' || SUBSTR(XSTR, 13,4) || '-' || SUBSTR(XSTR, 17,4) || '-' || SUBSTR(XSTR, 21,4) || '-' || SUBSTR(XSTR, 24,4) || '-' || SUBSTR(XSTR, 28,4); END RANDOM_GUID; 

Example GUID assigned by the function above:
8EA4-196D-BC48-9793-8AE8-5500-03DC-9D04

Answer 7

An example is found at: 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 

Answer 8

If you need inconsistent guides, you can send the results of sys_guid() via the hash function (see https://stackoverflow.com/a/115478/ ). The idea is to preserve any uniqueness used in the original creation and get something with more mixed bits.

For example:

 LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) 

An example showing a sequential guid by default against sending it through a hash:

 SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL UNION ALL SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL 

exit

 80c32a4fbe405707e0531e18980a1bbb 80c32a4fbe415707e0531e18980a1bbb 80c32a4fbe425707e0531e18980a1bbb 80c32a4fbe435707e0531e18980a1bbb c0f2ff2d3ef7b422c302bd87a4588490 d1886a8f3b4c547c28b0805d70b384f3 a0c565f3008622dde3148cfce9353ba7 1c375f3311faab15dc6a7503ce08182c