Universal Unique Identifier (UUID)
Oracle sequences are frequently used to provide unique numbers for primary keys where an appropriate unique key is not available. The use of sequences can cause a problem during data migrations and replication processes where duplication of the sequences occur. The use of a Universal Unique Identifier (UUID) solves this problem as each number is unique, rather than the next value in a sequential list of numbers. In addition, the UUID can be generated within the application server layer rather than the database, negating the need for extra round-trips to query the value of sequences or automatically assigned primary key values.
A UUID is a 128-bit number used to uniquely identify an object or entity on the Internet. Depending on the specific mechanisms used, a UUID is either guaranteed to be different or is, at least, extremely likely to be different from any other UUID generated until 3400 A.D. The UUID relies upon a combination of components to ensure uniqueness. A guaranteed UUID contains a reference to the network address of the host that generated the UUID, a timestamp (a record of the precise time of a transaction), and a randomly generated component. Because the network address identifies a unique computer, and the timestamp is unique for each UUID generated from a particular host, those two components should sufficiently ensure uniqueness. However, the randomly generated element of the UUID is added as a protection against any unforseeable problem.
In this article I'll present three methods for generating UUIDs.
An alternatively to a UUID is the
SYS_GUID() function provided by Oracle to produce a Globally Unique Identifier, their equivalent of a UUID.
SELECT SYS_GUID() FROM dual; SYS_GUID() -------------------------------- E7FBCFDD32B4B95BE0301A0A010AF268 1 row selected.
Using the Sun Microsystems implementation of the UUID and UUIDFactory classes that are available for download from OASIS ebXML Registry Reference Implementation Project (ebxmlrr). Once these are compiled and deployed in the application server layer new UUIDs can be generated using the following.
UUID uid = UUIDFactory.getInstance().newUUID(); System.out.println(uid.toString());
The UUIDDemo.java file provides an example of it's usage.
These classes, along with the UUIDGenerate class, should also be loaded into the database to allow UUIDs to be generated by DML and stored procedures. The classes can be compiled and loaded into the database using the loadjava executable.
loadjava -schema MY_SCHEMA -user sys/password@service -verbose UUID.java UUIDFactory.java UUIDGenerate.java
Alternatively, the source can be loaded into that database using SQL*Plus or Oracle Enterprise Manager (OEM).
Once the classes are loaded a call specification should be created to allow access to the Java from PL/SQL.
CREATE OR REPLACE FUNCTION NEW_UUID RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'UUIDGenerate.newUUID() return java.lang.String'; /
NEW_UUID function can then be tested.
CREATE TABLE uuids ( uuid VARCHAR2(40) NOT NULL, hash_code NUMBER(10) ); ALTER TABLE uuids ADD ( CONSTRAINT uuids_uk UNIQUE (uuid) ); TRUNCATE TABLE uuids; BEGIN FOR i IN 1 .. 100000 LOOP INSERT INTO uuids (uuid, hash_code) VALUES (NEW_UUID, NULL); END LOOP; COMMIT; END; /
The text represenation of the UUID stored in the database is 36 characters. If space is an issue this can be reduced by 4 characters by stripping the '-' from the
toString() method in the UUID class.
An alternative method is to create a "fake" UUID using PL/SQL.
CREATE OR REPLACE FUNCTION new_uuid RETURN VARCHAR2 AS l_seed BINARY_INTEGER; l_random_num NUMBER(5); l_date VARCHAR2(25); l_random VARCHAR2(4); l_ip_address VARCHAR2(12); BEGIN l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS')); DBMS_RANDOM.initialize (val => l_seed); l_random_num := TRUNC(DBMS_RANDOM.value(low => 1, high => 65535)); DBMS_RANDOM.terminate; l_date := conversion_api.to_hex(TO_NUMBER(TO_CHAR(SYSTIMESTAMP,'FFSSMIHH24DDMMYYYY'))); l_random := RPAD(conversion_api.to_hex(l_random_num), 4, '0'); l_ip_address := conversion_api.to_hex(TO_NUMBER(REPLACE(NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), '126.96.36.199'), '.', ''))); RETURN SUBSTR(l_date, 1, 8) || '-' || SUBSTR(l_date, 9, 4) || '-' || SUBSTR(l_date, 13, 4) || '-' || RPAD(SUBSTR(l_date, 17), 4, '0') || '-' || RPAD(l_random || l_ip_address, 12, '0'); END; /
This requires the conversion_api package.
For more information see:
Hope this helps. Regards Tim...