How to Create UUID in Oracle Database

-- Generate a GUID (returns RAW(16))
SELECT SYS_GUID() FROM DUAL;
-- Result: 550E8400E29B41D4A716446655440000 (hex format)

-- Create table with GUID as primary key
CREATE TABLE users (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    email VARCHAR2(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert without specifying ID (auto-generated)
INSERT INTO users (name, email) 
VALUES ('John Doe', 'john@example.com');

-- Query by GUID
SELECT * FROM users 
WHERE id = HEXTORAW('550E8400E29B41D4A716446655440000');

-- Commit changes
COMMIT;

-- Function to convert RAW to UUID string format
CREATE OR REPLACE FUNCTION RAW_TO_UUID(p_raw IN RAW)
RETURN VARCHAR2
IS
    v_hex VARCHAR2(32);
BEGIN
    v_hex := RAWTOHEX(p_raw);
    RETURN LOWER(
        SUBSTR(v_hex, 1, 8) || '-' ||
        SUBSTR(v_hex, 9, 4) || '-' ||
        SUBSTR(v_hex, 13, 4) || '-' ||
        SUBSTR(v_hex, 17, 4) || '-' ||
        SUBSTR(v_hex, 21, 12)
    );
END;
/

-- Function to convert UUID string to RAW
CREATE OR REPLACE FUNCTION UUID_TO_RAW(p_uuid IN VARCHAR2)
RETURN RAW
IS
BEGIN
    RETURN HEXTORAW(REPLACE(p_uuid, '-', ''));
END;
/

-- Usage examples
SELECT RAW_TO_UUID(SYS_GUID()) AS uuid FROM DUAL;
-- Result: 550e8400-e29b-41d4-a716-446655440000

SELECT UUID_TO_RAW('550e8400-e29b-41d4-a716-446655440000') AS raw FROM DUAL;
-- Result: 550E8400E29B41D4A716446655440000

-- Create base table
CREATE TABLE products (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    name VARCHAR2(200) NOT NULL,
    sku VARCHAR2(50) UNIQUE,
    price NUMBER(10,2)
);

-- Create view with formatted UUIDs
CREATE OR REPLACE VIEW products_view AS
SELECT 
    RAW_TO_UUID(id) AS id,
    name,
    sku,
    price
FROM products;

-- Insert data
INSERT INTO products (name, sku, price) 
VALUES ('Laptop', 'LAP-001', 999.99);

-- Query view (displays formatted UUID)
SELECT * FROM products_view;

-- Query base table by UUID string
SELECT * FROM products 
WHERE id = UUID_TO_RAW('550e8400-e29b-41d4-a716-446655440000');

-- B-tree index on UUID primary key (automatic)
CREATE TABLE orders (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    customer_id RAW(16) NOT NULL,
    total NUMBER(10,2)
);

-- Create index on foreign key
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index
CREATE INDEX idx_customer_created 
ON orders(customer_id, created_at);

-- Function-based index for UUID conversion
CREATE INDEX idx_users_uuid_format 
ON users(RAW_TO_UUID(id));

-- Check index usage
SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'USERS';

-- Analyze table statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA', 'USERS');

-- Table with both UUID and sequence number
CREATE TABLE transactions (
    id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    transaction_number NUMBER GENERATED ALWAYS AS IDENTITY,
    amount NUMBER(10,2),
    description VARCHAR2(500)
);

-- UUID for global uniqueness, sequence for human-readable ordering
INSERT INTO transactions (amount, description)
VALUES (150.00, 'Payment received');

-- Query combining both
SELECT 
    RAW_TO_UUID(id) AS uuid,
    transaction_number,
    amount,
    description
FROM transactions
ORDER BY transaction_number;

-- Procedure to create user and return UUID
CREATE OR REPLACE PROCEDURE create_user (
    p_name IN VARCHAR2,
    p_email IN VARCHAR2,
    p_uuid OUT VARCHAR2
)
IS
    v_id RAW(16);
BEGIN
    v_id := SYS_GUID();
    
    INSERT INTO users (id, name, email)
    VALUES (v_id, p_name, p_email);
    
    p_uuid := RAW_TO_UUID(v_id);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Usage
DECLARE
    v_new_uuid VARCHAR2(36);
BEGIN
    create_user('Alice Smith', 'alice@example.com', v_new_uuid);
    DBMS_OUTPUT.PUT_LINE('Created user with UUID: ' || v_new_uuid);
END;
/

import java.sql.*;
import java.util.UUID;

public class OracleUuidExample {
    
    public static void insertUser(Connection conn, String name, String email) 
            throws SQLException {
        String sql = "INSERT INTO users (id, name, email) VALUES (?, ?, ?)";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // Generate UUID
            UUID uuid = UUID.randomUUID();
            byte[] bytes = convertUuidToBytes(uuid);
            
            pstmt.setBytes(1, bytes);
            pstmt.setString(2, name);
            pstmt.setString(3, email);
            pstmt.executeUpdate();
        }
    }
    
    private static byte[] convertUuidToBytes(UUID uuid) {
        byte[] bytes = new byte[16];
        long msb = uuid.getMostSignificantBits();
        long lsb = uuid.getLeastSignificantBits();
        
        for (int i = 0; i < 8; i++) {
            bytes[i] = (byte) (msb >>> (56 - i * 8));
            bytes[i + 8] = (byte) (lsb >>> (56 - i * 8));
        }
        return bytes;
    }
}

import cx_Oracle
import uuid

# Connect to Oracle
conn = cx_Oracle.connect('user/password@localhost:1521/XEPDB1')
cursor = conn.cursor()

# Generate UUID and convert to bytes
user_uuid = uuid.uuid4()
uuid_bytes = user_uuid.bytes

# Insert with UUID
cursor.execute("""
    INSERT INTO users (id, name, email)
    VALUES (:id, :name, :email)
""", {
    'id': uuid_bytes,
    'name': 'Bob Wilson',
    'email': 'bob@example.com'
})

conn.commit()

# Query and convert back to UUID
cursor.execute("SELECT id, name FROM users WHERE ROWNUM = 1")
row = cursor.fetchone()
stored_uuid = uuid.UUID(bytes=bytes(row[0]))

print(f"Stored UUID: {stored_uuid}")

cursor.close()
conn.close()