Provides you with an in-depth understanding of Oracle random number generation functions.

Oracle random number generation function can return a random number between 0 and 1. This function has good practical value. Let's take a look at the usage of Oracle random number generation function.

Oracle8 version 8.0 introduces the DBMS_RANDOM package and Oracle8i 8.1.6 introduces the new features of the DBMS_RANDOM package. However, the Oracle8i documentation does not provide a detailed description of its functions. Fortunately, a new DBMS_RANDOM packet function can return a random number between 0 and 1. The new Oracle random number generation function is:
◆ FUNCTION value return number;
◆ FUNCTION value (low in number, high in number) return number;
◆ FUNCTION normal return number;
◆ FUNCTION string (opt char, len NUMBER) RETURN VARCHAR2;

The first form of the VALUE function returns a random number greater than or equal to 0 and less than 1; the second form returns a random number greater than or equal to LOW, less than HIGH. The following is an example of its usage:

 
 

  
  
SQL> select dbms_random.value, dbms_random.value(55,100) from dual;   
  
  
VALUE            DBMS_RANDOM.VALUE(55,100)   
  
  
--------------- -----------------------------   
  
  
.782821936       79.6367038    
 
 

The NORMAL function returns a group of numbers that follow the NORMAL distribution. The standard deviation of this normal distribution is 1, and the expected value is 0. 68% of the values returned by this function are between-1 and + 1, 95% between-2 and + 2, and 99% between-3 and + 3. In fact, this is what you see in Listing 1.

Finally, it is the STRING function. It returns a random string of 60 characters. The OPT parameter can be any single character in the value displayed in Listing 2.

The Oracle random number generation function and DBMS_RANDOM package files are included in SQLPlus:

 
 

  
  
select text   from all_source    
  
  
       where name = 'DBMS_RANDOM'    
  
  
       and type = 'PACKAGE' order by line;   
 
 

Use of oracle custom functions

Oracle TRIM function syntax

Oracle date functions

Introduction to Oracle System Variable Functions

Syntax for creating an Oracle package