1. SQL Reference Guide
  2. Built-in Functions
  3. Function to generate a UUID string

Function to generate a UUID string

The function random_uuid returns a randomly generated UUID, as a string of 36 characters. This function can be used to generate values for columns of type UUID in an INSERT or UPDATE SQL statement.

Syntax:
string random_uuid()

Semantics:

  • This function does not expect any input argument.
  • return type: string

Example:

In certain applications like maintaining student records in a university, you can auto-generate the ID instead of providing the value. Consider a simple table schema with an id as the primary key and a name column.

CREATE TABLE myTable (id STRING AS UUID, name STRING, PRIMARY KEY (id))

Insert the following data into the table. You can use the random_uuid function to generate the primary key values. 

INSERT INTO myTable values(random_uuid(),"Adam")
INSERT INTO myTable values(random_uuid(),"Lily")

Explanation:

The random_uuid function internally generates unique UUID during the INSERT operation and assigns them to the id fields. Run the query to select the elements from the table. 
select * from myTable order by name
Output:
{"id":"ff7057c2-cda9-4f6b-b94f-227b259a94d3","name":"Adam"}
{"id":"37166790-4470-4484-bfbb-66364e0ff807","name":"Lily"}

The output of the query displays the assigned UUID values against the student names.

The random_uuid function generates a random but unique 36-byte string. Consider the multi-region tables where the identity columns are unique to a single region. You can use the random_uuid function to generate a globally unique identity during the record insertion.

You can retrieve the UUID of the inserted record by using the RETURNING clause in the INSERT or UPDATE statement as follows:
INSERT INTO myTable values(random_uuid(),"Adam") RETURNING *
Output:
{"id":"9f05eb60-2fa7-4c32-a90a-64371961cb9d","name":"Adam"}

For more details on the RETURNING clause, see the Upsert statement

You can retrieve the generated value of an identity column using class methods from various Oracle NoSQL Database Drivers.

For example: If your application is using the Java SDK, you can use the getGeneratedValue() method, which returns the generated value if the operation creates a new value for an identity column. For more details, see PutResult.

If your application is using the Python SDK, you can use the get_return_row() method, which succeeds only if the row exists. For more details, see PutRequest.

Similarly, each language SDK exposes an interface for retrieving the generated value.