Inserting data to DB using system-uuid

Question

I have run into a problem related to System generated uniquer identifier.

Below is the definition of a datamember from my bean class:

@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
@Column(name = "id")
private String id;

Whenever the client action is invoked, data will be persisted to DB based with the value for id attribute generated dynamically using above system-uuid. Now I have a requirement wherein i have to perform mass insert to this table. As there is no import functionality, is there any way that i can perform the bulk insert from backend using oracle insert queries? If so, how do i mention the value for the column 'id' so that it works like the same as the insert happened from client.

Use SYS_GUID https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions153.htm. — ibre5041, Apr 26 '16 at 14:17

score 1 · Accepted Answer · edited May 23 '17 at 12:13

The usage of SYS_GUID is very effective for bulk inserts as simulated below on a small example

INSERT INTO TARGET_TABLE
select 
rawtohex(sys_guid()) uuid
from dual connect by level <= 3
;

The only (optical) problem is, that the generated string is not formatted as expected from the Java uuid.

UID                           
--------------------------------
BBEDBA2A9BB44952AA5EE311D0880C39 
B95CA374AC7C4619A68543771101D590 
6B4E89CF0307427BBD047E42BD6DFBB0

A simple subquery and some substrs with a concatenation brings the remedy - see here:

with t as (
  select rawtohex(sys_guid()) guid from dual
  connect by level <= 3
)
select guid, lower(substr(guid, 1, 8)
  ||'-'|| substr(guid, 9, 4)
  ||'-'|| substr(guid, 13, 4)
  ||'-'|| substr(guid, 17, 4)
  ||'-'|| substr(guid, 21, 12)) as formatted_guid
from t;


GUID                             FORMATTED_GUID                     
-------------------------------- ------------------------------------
993712381A8543FB9320C587D078330E 99371238-1a85-43fb-9320-c587d078330e 
FFF3CF7104E048BF8E6677B080BD35A6 fff3cf71-04e0-48bf-8e66-77b080bd35a6 
1FBCC67EA46B4474B26128F2DDAA519B 1fbcc67e-a46b-4474-b261-28f2ddaa519b

The reason behind is that Oracle's UUID is RAW(16), with is more effective way of storing instead of CHAR(36). — ibre5041, Apr 28 '16 at 18:25

Inserting data to DB using system-uuid

1 Answers1