DBMS Random Reference

: Joel Lipman; Oracle PL/SQL; 21 August 2012; Hits: 7274

What?
How to use the randomization features of Oracle PL/SQL.

Why?
I've been tasked with writing a stored procedure that scrambles data for developers working with some of our databases containing sensitive data.

How?

copy raw

SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;

SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;

option1 can be:

RANDOM
VALUE
STRING

option2 can be:

U (for Uppercase)
L (for Lowercase)
A (for AlphaNumeric)
X (for Alphanumeric with upper case alphabets)
P (for Printable characters only)

Usage

copy raw

select dbms_random.random from dual;  
-- yields random number, eg. 68489408

select dbms_random.value from dual;  
-- yields values between 0 and 1

select dbms_random.value(1,1000) myAlias from dual; 
-- yields number between 1 and 1000

select dbms_random.string('U', 10) myAlias from dual;  
-- yields uppercase string of 10 letters

select dbms_random.string('L', 10) myAlias from dual;  
-- yields lowercase string of 10 letters

select dbms_random.string('A', 10) myAlias from dual;  
-- yields string of 10 mixed upper and lowercase letters 

select dbms_random.string('X', 10) myAlias from dual;  
-- yields string of 10 mixed uppercase letters and digits

select dbms_random.string('P', 10) myAlias from dual;  
-- yields string of 10 printable characters (includes symbols/punctuation)