Re: Randomizing ...

From: David Fitzjarrell <oratune_at_aol.com>
Date: Tue, 14 Nov 2000 19:53:21 GMT
Message-ID: <8us57c$ibv$1@nnrp1.deja.com>

In our last gripping episode sergey_s_at_my-deja.com wrote:
> How can I select a number of random rows from a table? Say I wanted to
> select 500 random IDs from a table, how would I do that? I was trying
> to find a random number generator function in SQL or PL/SQL, but
 didn't
> see one so far. I guess I could use mod. Are there any other ways?
>
> Oracle 8.0.5
>
> Thank you!
> Sergey
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

There is a package to generate random numbers in 8.0 and later releases of Oracle. It is called DBMS_RANDOM. There is also a package called DBMS_CRYPTO_TOOLKIT that needs the DBMS_RANDOM package to function so it is bundled with the installation script for the DBMS_CRYPTO_TOOLKIT package although it can be installed outside of that context without problems. The package needs to be installed by SYS or by CONNECT INTERNAL through Server Manager.

The DBMS_RANDOM package consists of four subprograms:

INITIALIZE
SEED
RANDOM
TERMINATE The following is from the Oracle documentation on the package:

INITIALIZE Procedure
To use the package, first call the initialize subprogram with the seed to use.

Syntax
DBMS_RANDOM.INITIALIZE (
   seed IN BINARY_INTEGER);

---

Note:
Use a seed that is sufficiently large, more than 5 digits. A single digit might not return sufficiently random numbers.

---

Parameters
Table 33-2 INITIALIZE Procedure Parameters Parameter Description
seed

  Seed number used to generate a random number.

SEED Procedure
This procedure resets the seed.

Syntax
DBMS_RANDOM.SEED (
   seed IN BINARY_INTEGER);

Parameters
Table 33-3 INITIALIZE Procedure Parameters Parameter Description
seed
 Seed number used to generate a random number.

RANDOM Function
This function gets the random number.

Syntax
DBMS_RANDOM.RANDOM
   RETURN BINARY_INTEGER; Parameters
None.

Example
my_random_number := Random;

TERMINATE Procedure
When you are finished with the package, call the TERMINATE procedure.

Syntax
DBMS_RANDOM.TERMINATE; Parameters
None.

This should be what you are looking for.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Nov 14 2000 - 13:53:21 CST