Oracle dbms_random varrays

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle dbms_random

Oracle Database Tips by Donald Burleson

Using Oracle dbms_random

This uses the dbms_random. function to randomize a number from which the character string is constructed. From the random number obtained with dbms_random, we will take the 3 numbers from the third position and check if they exist within 33 and 125, the allowable numbers for ASCII printable characters.

Of course after all the work with dbms_random I figured out that it would have been easier just to cycle through the possible values for the varrays in a nested looping structure. Of course that would have led to a non-random ordered table. For the work we were doing it would have been ok to do it that way, but I can see other situations where the dbms_random technique would be beneficial.

My thought was to use varray types and populate them with the various possible values, then use dbms_random.value to generate the index values for the various varrays. The count column was just a truncated call to dbms_random in the range of 1 to 600. All of this was placed into a procedure with the ability to give it the number of required values.

set dbms_rabdom seed to loop interator;
insert using calls to dbms_random.value(1-n) as the indice for the various varrays;
end loop;
commit;
end;

Oracle DBMS_RANDOM.STRING is part of the Oracle DBMS_RANDOM PACKAGE

Function	Returns
DBMS_RANDOM.STRING ('L', 10)	abcdefghijk
DBMS_RANDOM.STRING ('U', 10)	ABCDEFGHIJK
DBMS_RANDOM.STRING ('A', 10)	AbBbcCDdeE
DBMS_RANDOM.STRING ('X', 10)	A2B1C34D5E
DBMS_RANDOM.STRING ('P', 10)	a2AB'g$-D3

For more information about DBMS_RANDOM.VALUE and related topics, please visit the following links:

The DBMS_RANDOM Package
DBMS_RANDOM.VALUE

��