Use DBMS_RANDOM to generate text and date values

by Bob Watkins | May 09, 2006 7:00:00 AM

Tags: Databases, Bob Watkins, integer, SELECT DBMS_RANDOM.VALUE, Oracle Corp., DBMS_RANDOM, random date, Oracle Tips Newsletter

Takeaway: The PL/SQL package DBMS_RANDOM can generate random text and date values as well as numbers. Check out these code examples to learn how you can generate these values in your development work.

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.

Print/View all Posts Comments on this article

Example Requires Oracle 10g JohnnySacks | 05/10/06

Good point bwatkins | 05/11/06

techrepublic ads are blocking the text... JustAnotherGuy | 08/25/06