Random Character Generator in Oracle

Oracle dbms_random package can generate random characters in character, numeric or alphanumeric formats.

dbms_random.value(Parameter, Value)

• U – Upper case
• L – Lower case
• A – Alphanumeric
• X – Alphanumeric with upper case alphabets.
• P – Printable characters only.

Create Random Characters

SQL> select dbms_random.random value from dual;
Value : 531879542

SQL> select dbms_random.value from dual;
Value : 1

SQL> select dbms_random.value(100,1000) valuefrom dual;
Value : 452

SQL> select dbms_random.value(100000000000, 999999999999) value from dual;
Value : 515055428760

SQL> select dbms_random.string(‘U’, 10) str from dual;
Value : KGSQLEKTSX

SQL> select dbms_random.string(‘L’, 10) str from dual;
Value = ksfkwqwerv

SQL> select dbms_random.string(‘A’, 10) str from dual;
Value : RhjE+RojjL

SQL> select dbms_random.string(‘X’, 10) str from dual;
Value : GF:E3B4GRB

SQL> select dbms_random.string(‘P’, 10) str from dual;
Value : *Yw(IK/zsj

DBMS_RANDOM is a good utility and will find its way into lot of development projects, especially web based ones. However, this Package is not exhaustively documented. One should not use it just for the sake of it being there. Make sure that there is a true requirement or a necessity of random values before making use of this package. If you already have a custom code meant for the same purpose, check out the benefits that are available when using this package compared to your application.