| Databases are supposed to be about predictable facts; we meticulously collect data that defines our business or activity and then go to any lengths to protect and enforce its integrity. Why would you want to generate something that is just the contraryan unpredictable, random value? Let's look at some common situations in which DBAs might need a randomizer: You are creating temporary passwords or generating web site user IDs for registered users. You are involved in the development of an application that needs thorough testing from all angles (performance, scalability, accuracy, and more), and as part of that development, you must create a test bed with some representative data. Representative here means that the data has to relate in some way to the actual entity it represents; for example, a column containing an account number must reflect the format of an actual account number, a name should be alphabetic, and so on. You want to test the effect of some important database structural components such as indexes. Answers to questions such as how many indexes, on what columns, how many histograms you should collect while analyzing, whether it will be beneficial to enable block sampling, and so on, all require a lot of data representing real-life scenarios. It might be impractical to get this set of data from another source, so you might need to generate it. The focus here is on creating a data set that is random enough but that closely follows a real-life pattern. You are building an encryption infrastructure and need to generate a key. Oracle9i Database and later releases provide ways to generate true random keys ; in prior versions, however, you will need to generate your own keys.
This chapter explains how to use PL/SQL to generate random values (numbers and strings) representing real-world values. It also explores the use of seeding techniques to randomize generated values. The chapter contains extensive examples and code samples, built around the need to generate test data (account names and balances) for a banking application, which you can easily use in your own development work. Most of the examples shown in this chapter take advantage of Oracle's built-in package, DBMS_RANDOM. Two key functions provided in this package return random values as follows:
For numbers The VALUE function returns a positive floating-point number greater or equal to zero and less than 1 with 38 digits of precisionfor example, 0.034869472.
For characters The STRING function returns a random string of characters of a user-specified length and distribution of characters.
I'll show examples of using these functions, and the other functions provided in DBMS_RANDOM, throughout this chapter. |
