DBA Hub

📋Steps in this guide1/3

UUID() Function in Oracle Database 23ai/26ai

Oracle database 23ai/26ai (23.9) introduced the UUID() function that returns a 128-bit universal unique identifier, which can be used to generate a primary key value.

oracle 23configurationintermediate
by OracleDba
31 views
1

Using the UUID() Function

The function returns a version 4 variant 1 UUID (see UUID RFC 9562 ). The return value is a , but most tools will visualize it as a 32 character string. It can accept a version specifier with allowable values of 0 and 4. According to the documentation, this doesn't affect the UUID that is returned, and both are functionally equivalent of calling with no parameter. Similar to the function, if we want to store the result of the function, we need to create a column as a .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select uuid();

UUID()
--------------------------------
D56FEA0F4D1F4FECBF08EA362C38E1F3

SQL>

select uuid(0), uuid(4);

UUID(0)                          UUID(4)
-------------------------------- --------------------------------
17B3E68017E44F84BF51ABB27AD5C410 58605C8F80834F6BBF9BE42807B484BC

SQL>

drop table if exists t1;
create table t1 (
  id           raw(16),
  description  varchar2(50)
);


insert into t1 (id, description)
select uuid(),
       'Description for ' || level
from   dual
connect by level <= 10000;

commit;
2

Conversion Functions

Tools will typically convert the RAW(16) value of the function to a 32 character string to visualize it. Alternatively we use the function to convert it to a 36 character string, which includes 4 hyphens. We can alter format of the output using regular string functions as required. In the following example we remove the hyphens and convert it to upper case. When we have a string representation of the UUID, we can convert it back to the value using the function. This handles mixed case and hyphens, or the lack of them. In the example below we present the same UUID string in several formats, and in each case they get converted to the same UUID value.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
select raw_to_uuid(uuid());

RAW_TO_UUID(UUID())
------------------------------------
48a8b091-2587-4fd4-bf04-877d259793de

SQL>


select raw_to_uuid(id)
from   t1
where  rownum <= 5;

RAW_TO_UUID(ID)
------------------------------------
72d031c1-2da1-4fe9-bf2d-1d41296d3c25
6b9c512a-4129-4fdd-bf88-9313df501959
f573c8d6-0dea-4fb3-bfb2-eb24bb11af69
37e7d88a-463c-4fc4-bf6c-b4bbbf38636e
4d368468-0ec9-4ffc-bf19-706fff200f94

SQL>

select upper(replace(id, '-', '')) as id
from   t1
where  rownum <= 5;

ID
------------------------------------
72D031C12DA14FE9BF2D1D41296D3C25
6B9C512A41294FDDBF889313DF501959
F573C8D60DEA4FB3BFB2EB24BB11AF69
37E7D88A463C4FC4BF6CB4BBBF38636E
4D3684680EC94FFCBF19706FFF200F94

SQL>

set linesize 100

with data as (
  select id, id_type
  from   (values 
           ('72d031c1-2da1-4fe9-bf2d-1d41296d3c25', 'Lowercase Hyphens'),
           ('72D031C1-2DA1-4FE9-BF2D-1D41296D3C25', 'Uppercase Hyphens'),
           ('72D031C1-2Da1-4Fe9-Bf2D-1D41296D3C25', 'Mixed Case Hyphens'),
           ('72d031c12da14fe9bf2d1d41296d3c25', 'Lowecase No Hyphens'),
           ('72D031C12DA14FE9BF2D1D41296D3C25', 'Uppercase No Hyphens'),
           ('72D031C12Da14Fe9Bf2D1D41296D3C25', 'Mixed Case Mo Hyphens')
         ) as raw_data (id, id_type)
)
select id, id_type, uuid_to_raw(id)
from   data;

ID                                   ID_TYPE               UUID_TO_RAW(ID)
------------------------------------ --------------------- --------------------------------
72d031c1-2da1-4fe9-bf2d-1d41296d3c25 Lowercase Hyphens     72D031C12DA14FE9BF2D1D41296D3C25
72D031C1-2DA1-4FE9-BF2D-1D41296D3C25 Uppercase Hyphens     72D031C12DA14FE9BF2D1D41296D3C25
72D031C1-2Da1-4Fe9-Bf2D-1D41296D3C25 Mixed Case Hyphens    72D031C12DA14FE9BF2D1D41296D3C25
72d031c12da14fe9bf2d1d41296d3c25     Lowecase No Hyphens   72D031C12DA14FE9BF2D1D41296D3C25
72D031C12DA14FE9BF2D1D41296D3C25     Uppercase No Hyphens  72D031C12DA14FE9BF2D1D41296D3C25
72D031C12Da14Fe9Bf2D1D41296D3C25     Mixed Case Mo Hyphens 72D031C12DA14FE9BF2D1D41296D3C25

6 rows selected.

SQL>
3

PL/SQL Support

When using the function we can make direct assignments in PL/SQL. At the moment direct assignments using the function are not available from PL/SQL, but I'm sure that will be supported in a later release. Instead we have to call the function as part of a SQL or DML statement, which is supported in PL/SQL. For more information see: - UUID - RAW_TO_UUID - UUID_TO_RAW - SYS_GUID - Universal Unique Identifier (UUID) Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
declare
  l_uuid raw(16);
begin
  l_uuid := sys_guid();
end;
/

PL/SQL procedure successfully completed.

SQL>

declare
  l_uuid raw(16);
begin
  l_uuid := uuid();
end;
/
            *
ERROR at line 4:
ORA-06550: line 4, column 13:
PLS-00201: identifier 'UUID' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/

SQL>

declare
  l_uuid raw(16);
begin
  select uuid() into l_uuid;
end;
/

PL/SQL procedure successfully completed.

SQL>


begin
  insert into t1 (id, description)
  select uuid(),
         'Description for ' || level
  from   dual
  connect by level <= 10;
  
  commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!