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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I would recommend using Oracle s  quot dbms crypto randombytes quot  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  quot sys guid quot  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] How to generate a GUID in Oracle?

Examples related to oracle

Examples related to guid