How to generate a GUID in Oracle?

Question

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

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

score 149 · Accepted Answer · edited Oct 15 '14 at 10:36

149

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

edited Oct 15 '14 at 10:36

Kiquenet

13,820
33
141
236

answered Jun 14 '10 at 13:20

Tony Andrews

126,319
20
218
255

Weird, the sys_guid() is always giving me the same GUID.. Do I need to give a seed to the function or? – Acibi Jun 14 '10 at 13:45
11

Are you sure they are exactly the same? It does tend to return very similar (but different) values - e.g. when I just tried I got 88FDC68C75DEF955E040449808B55601 and 88FDC68C75DFF955E040449808B55601, which differ only at the 12th character! – Tony Andrews Jun 14 '10 at 13:54
Try select sys_guid() from dual and compare values. Modify answer about it. – Kiquenet Oct 15 '14 at 10:37

score 28 · Answer 2 · answered May 22 '11 at 06:33

28

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

answered May 22 '11 at 06:33

TTT

2,355
17
16

Thanks for alerting me to a useful Oracle feature I didn't know about. – SteveT May 07 '12 at 14:21

score 14 · Answer 3 · answered May 18 '18 at 14:24

14

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

answered May 18 '18 at 14:24

Zartch

867
9
24

2

Wish I could buy you a drink for this. +1 – Baodad Jan 14 '20 at 23:53
Fantastic! Thank you :) – ZooZ Aug 12 '21 at 11:19

score 8 · Answer 4 · answered Jun 14 '10 at 13:25

It is not clear what you mean by auto-generate a guid into an insert statement but at a guess, I think you are 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 that case I believe the ID column should be declared as RAW(16);

I am doing this off the top of my head. I don't have an Oracle instance handy to test against, but I think that is what you want.

MikeWyatt · Answer 5 · 2017-01-31T02:02:48.280

7

sys_guid() is a poor option, as other answers have mentioned. One way to generate UUIDs and avoid sequential values is to generate random hex strings yourself:

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;

edited Jan 31 '17 at 02:02

answered Jan 31 '17 at 01:43

MikeWyatt

7,812
10
47
70

5

Those are not guaranteed to be unique. You'd need to pair it with something like a unique constraint on wherever you're storing it, if you're storing it. (and generating a new number if you encounter a repeated value and violate the unique constraint, however unlikely that may be) – James Daily Jan 05 '18 at 01:09

score 6 · Answer 6 · answered Jan 31 '19 at 15:02

If you need non-sequential guids you can send the sys_guid() results through a hashing function (see https://stackoverflow.com/a/22534843/1462295 ). The idea is to keep whatever uniqueness is used from the original creation, and get something with more shuffled bits.

For instance:

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

Example showing default sequential guid vs 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

output

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

score 2 · Answer 7 · answered Nov 20 '13 at 19:42

2

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

answered Nov 20 '13 at 19:42

Gopinath Kotla

21
1

BERGUIGA Mohamed Amine · Answer 8 · 2017-11-27T13:31:57.597

you can use function bellow in order to generate 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 of GUID genedrated by the function above:
8EA4-196D-BC48-9793-8AE8-5500-03DC-9D04

SYS_GUID was not random enough for our needs, but this seems to pump GUID's that are way more random. — ThePeter, Dec 05 '18 at 16:19

Alex Fischer · Answer 9 · 2021-01-28T09:32:48.757

I would recommend using Oracle's "dbms_crypto.randombytes" function.

Why?
This function returns a RAW value containing a cryptographically secure pseudo-random sequence of bytes, which can be used to generate random material for encryption keys.

select REGEXP_REPLACE(dbms_crypto.randombytes(16), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5') from dual;

You should not use the function "sys_guid" because only one character changes.

ALTER TABLE locations ADD (uid_col RAW(16));

UPDATE locations SET uid_col = SYS_GUID();

SELECT location_id, uid_col FROM locations
   ORDER BY location_id, uid_col;

LOCATION_ID UID_COL
----------- ----------------------------------------------------------------
       1000 09F686761827CF8AE040578CB20B7491
       1100 09F686761828CF8AE040578CB20B7491
       1200 09F686761829CF8AE040578CB20B7491
       1300 09F68676182ACF8AE040578CB20B7491
       1400 09F68676182BCF8AE040578CB20B7491
       1500 09F68676182CCF8AE040578CB20B7491

https://docs.oracle.com/database/121/SQLRF/functions202.htm#SQLRF06120

Oranit Dar · Answer 10 · 2020-12-13T07:29:39.497

0

Creating a 350 characters GUID:

dbms_random.STRING ('a', 350) - returning string in mixed case alpha characters

dbms_random.STRING ('x', 350) - returning string in uppercase alpha-numeric characters

edited Dec 13 '20 at 07:29

answered Dec 10 '20 at 08:51

Oranit Dar

1,281
15
16

How to generate a GUID in Oracle?

10 Answers10

Linked