Numbers, text strings, and dates are three common datatypes used in tables. Although you would expect the
PL/SQL package DBMS_RANDOM to be able to generate random numbers—and it does—it
is also capable of generating random text and date values as well.
Let’s start with numbers. The VALUE function returns a
number greater than or equal to zero, and less than 1, with 38 digits of
precision.
SELECT DBMS_RANDOM.VALUE FROM DUAL;
For integers in a given range, add the arguments low_value and high_value, and
truncate the decimals from the result (the high value is not included as a possible value). So, for integers from 0 to 99,
you would use this code:
SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;
To generate random text strings, use the STRING function and
supply a code indicating the type of string and the desired length:
SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;
The type codes are documented in the Oracle Database 10g PL/SQL Packages and Types Reference. Here are a few type codes: ‘U’ generates uppercase, ‘L’ generates
lowercase, and ‘A’ generates mixed case.
Oracle stores dates as integer offsets from a key date in
the past (January 1, 4712 B.C., in case you were curious). This means that you
can generate random dates in a given range by finding the integer that
corresponds to your desired start date, and then adding a random integer to it.
You can generate the internal date number for today’s date
by using the TO_CHAR function with the ‘J’ format code:
SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;
For example, to generate random dates during the year 2003,
you would first determine the date integer for January 1, 2003:
SELECT TO_CHAR(TO_DATE('01/01/03','mm/dd/yy'),'J')
FROM DUAL;
The system responds with 2452641. So, to generate a random
date within the year, we use DBMS_RANDOM.VALUE with a low_value
of 2452641 and a high_value of 2452641+364, and
convert it to a date:
SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')
FROM DUAL;
Listing A puts this all together. It creates a sample table
called random_stuff that has three columns: ID,
date1, and text1. It then inserts 100 rows into it, using a combination of
ROWNUM and DBMS_RANDOM to generate the data. You can use any table with at
least 100 rows in it for the subquery, since no columns
from the actual table are in the SELECT list.
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
