Generating a Random Password in Oracle (with at least one special character)

🔹 Generating a Random Password in Oracle (with at least one special character)

To generate a random password in Oracle that meets the following criteria:
✅ At least one special character
✅ Contains uppercase, lowercase, numbers
✅ Has a specific length

---

✅ 1️⃣ Using DBMS_RANDOM with At Least One Special Character

SELECT 
    SUBSTR(DBMS_RANDOM.STRING('X', 1), 1, 1) ||  -- 1 Uppercase Letter
    SUBSTR(DBMS_RANDOM.STRING('L', 1), 1, 1) ||  -- 1 Lowercase Letter
    SUBSTR(DBMS_RANDOM.STRING('U', 1), 1, 1) ||  -- 1 Digit
    SUBSTR(DBMS_RANDOM.STRING('P', 1), 1, 1) ||  -- 1 Special Character
    DBMS_RANDOM.STRING('X', 4) AS random_password  -- 4 More Random Characters
FROM DUAL;

🔹 Explanation:

• 'X' → Mix of letters (uppercase + lowercase)
• 'L' → Lowercase letter
• 'U' → Numeric digit
• 'P' → Special character (!@#$%^&*()_+=-)
• Adds 4 more random characters to ensure randomness

🟢 Example Output

Rr2@aP5t

---

✅ 2️⃣ Custom Function to Generate a Secure Password

If you need a reusable function:

CREATE OR REPLACE FUNCTION generate_random_password(p_length NUMBER DEFAULT 10) RETURN VARCHAR2 IS
    v_password VARCHAR2(50);
BEGIN
    -- Ensure at least one uppercase, one lowercase, one digit, and one special character
    v_password := 
        SUBSTR(DBMS_RANDOM.STRING('X', 1), 1, 1) ||  -- Uppercase
        SUBSTR(DBMS_RANDOM.STRING('L', 1), 1, 1) ||  -- Lowercase
        SUBSTR(DBMS_RANDOM.STRING('U', 1), 1, 1) ||  -- Number
        SUBSTR(DBMS_RANDOM.STRING('P', 1), 1, 1) ||  -- Special Character
        DBMS_RANDOM.STRING('X', p_length - 4);  -- Fill remaining length

    RETURN v_password;
END generate_random_password;
/

Usage:

SELECT generate_random_password(12) FROM DUAL;

🔹 This generates a 12-character random password with at least one special character.

🟢 Example Output

Jr3#GzXv9M2Q

---

🔹 Summary

Method	Query / Function	Output Example
Quick SQL Query	`DBMS_RANDOM.STRING('X', 1)
Reusable Function	generate_random_password(12)	Jr3#GzXv9M2Q