Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Cloning Data Accurately and Randomly-2 : Page 2

Using the DBMS_RANDOM Package
Your first step is to create a random number generator. Prior to the release of Oracle 8.0, developers had to create their own. Starting with version 8.0 Oracle introduced the DBMS_RANDOM package, which provides a built-in random number generator. It is faster than generators written in PL/SQL because it calls Oracle's internal random number generator. By contrast, custom PL/SQL generators have to execute multiple lines of math calculations to come up with a random number.

This package is not installed by default. It has to be created by a SYS user running the Catoctk.sql script located in the $ORACLE_HOME\Rdbms\Admin directory. This script executes several scripts in succession to ensure that DBMS_RANDOM is available for use. This package is granted to PUBLIC by default.

Your first step is to initialize the generator using the INITIALIZE procedure with a seed. Here is the syntax specification for this procedure:


Oracle recommends you use at least a five-digit value to generate sufficiently random numbers. In my code I am going to use the UID function, which uniquely identifies the current user, and multiply it by 10,000 to ensure that at least a five-digit value is used as the seed. This integer is generated by the Oracle RDBMS whenever a user connects to the database.

Here is the syntax:


You only need to run this procedure once. You use the initialization section of the custom package for that purpose. The initialization section is a powerful mechanism: PL/SQL detects automatically when this code should be run. You do not have to execute any statements explicitly, and you can be sure they are run only once. (Because initialization resets your sequence of random numbers and starts it from the beginning, if you kept calling the initialization procedure, you'd repeat the same numbers over and over again.)

The seed value can be changed programmatically anytime by calling the SEED procedure. The specification is:


To acquire a random number after the generator has been initialized, call the RANDOM function, which returns a random-value integer.

Listing 1 shows the code for creating the simple Rand package. Let's test it by executing following command five times in SQL*PLus:

SELECT Rand.RandomNum FROM Dual;

Here is the output I get from the above command (yours will differ):
Good enough.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date