• Oracle Random Number Example
• Oracle Generate Random Primary Key Definition
• Oracle Generate Random Integer

• May 25, 2004 Generating random numbers or strings is oft-times a necessity. Oracle provides a random number generator that is faster than writing your won random generation logic in PL/SQL, and can generate both character and alphanumeric strings. Perhaps it is time to learn more about the DBMSRANDOM package.
• May 25, 2004  The DBMSRANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the ORACLEHOME/rdbms/admin directory.
• Apr 02, 2007 I have a table which have 2 fields, one of those is the primary key, the other is the description. I have established the primary key as a DBSequence, but when I try to insert a new record testing the service, appears a negative number in the primary key and instead of that I want to appear the correspondent key, or if doesn't appear the correct value, the insert be done correctly.

Oracle sequences are frequently used to provide unique numbers for primary keys where an appropriate unique key is not available. The use of sequences can cause a problem during data migrations and replication processes where duplication of the sequences occur.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.

The DBMS_RANDOM package provides a built-in random number generator.

This chapter contains the following topics:

• • Operational notes

Note:

Using DBMS_RANDOM

Operational notes

• The RANDOM function produces integers in the range [-2^^31, 2^^31).

• The VALUE function produces numbers in the range [0,1) with 38 digits of precision.

DBMS_RANDOM can be explicitly initialized but does not require initialization before a call to the random number generator. It automatically initializes with the date, user ID, and process ID if no explicit initialization is performed.

If this package is seeded twice with the same seed, then accessed in the same way, it produces the same result in both cases.

In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling an overload of SEED. To produce different output for every run, simply omit the seed call. Then the system chooses a suitable seed for you.

Summary of DBMS_RANDOM subprograms

Table 6-1 DBMS_RANDOM package subprograms

Note:

Notes: /generate-100s-microsoft-keys-how-china.html.

• The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

• The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

INITIALIZE procedure

This procedure is deprecated. Although currently supported, it should not be used. It initializes the random number generator.

Parameters

Table 6-2 INITIALIZE procedure parameters

Usage notes

This procedure is obsolete as it simply calls the SEED procedure.

NORMAL function

This function returns random numbers in a standard normal distribution.

Return value

The random number, a NUMBER value

RANDOM function

This procedure is deprecated. Although currently supported, it should not be used. It generates and returns a random number.

Return value

A random BINARY_INTEGER value greater than or equal to -power(2,31) and less than power(2,31)

Usage notes

See the NORMAL function and the VALUE function.

SEED procedure

This procedure resets the seed used in generating a random number.

Parameters

Table 6-3 SEED procedure parameters

Usage notes

The seed can be a string up to length 2000.

STRING function

This function generates and returns a random string.

Parameters

Table 6-4 STRING function parameters

Oracle Random Number Example

Return value

A VARCHAR2 value with the random string

TERMINATE procedure

This procedure is deprecated. Although currently supported, it should not be used. It would be called when the user is finished with the package.

VALUE function

One version returns a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). The other version returns a random Oracle Database NUMBER value x, where x is greater than or equal to the specified low value and less than the specified high value.

Parameters

Table 6-5 VALUE function parameters

Oracle Generate Random Primary Key Definition

Return value

Oracle Generate Random Integer

A NUMBER value that is the generated random number