To generate random value in PL/SQL

To update a column with a random value in PL/SQL, you can use the DBMS_RANDOM package, which provides functions for generating random numbers or strings.

To update random number

BEGIN
    UPDATE employees
    SET salary = ROUND(DBMS_RANDOM.VALUE(100, 500));
    COMMIT;
END;
  • DBMS_RANDOM.VALUE(100, 500) generates a random decimal number between 100 and 500.
  • ROUND is used to convert the decimal value to an integer.

To update random string

BEGIN
   UPDATE employees
    SET password = DBMS_RANDOM.STRING('x', 8);  -- 'x' specifies alphanumeric characters
    COMMIT;
END;
  • DBMS_RANDOM.STRING('x', 8) generates an 8-character alphanumeric string.
  • ‘x’ specifies alphanumeric characters (letters and numbers). Other options include ‘a’ for alphabets and ‘u’ for uppercase only.

Read More