Is there any RANDOM functions in PL/SQL?

Weidong Zhou

unread,

Jan 7, 2000, 3:00:00 AM1/7/00

to

My PL/SQL procedure needs a RANDOM function like random( 0, 1000 ) to
generate random number between 0 and 1000. Anyone knows whether ORACLE
has this kind functions? Thanks a lot.

Weidong Zhou

Daniel

unread,

Jan 7, 2000, 3:00:00 AM1/7/00

to

No built in functions that I am aware of.

Daniel
Austin, Texas

"Weidong Zhou" <weidon...@sabre.com> wrote in message
news:38765E7C...@sabre.com...

Sybrand Bakker

unread,

Jan 7, 2000, 3:00:00 AM1/7/00

to

Oracle 8 has the dbms_random package, for Oracle 7 visit the website of
Thomas Kyte.

Hth,

--
Sybrand Bakker, Oracle DBA

Weidong Zhou

unread,

Jan 7, 2000, 3:00:00 AM1/7/00

to

Daniel,
I found something about random number on Internet. Oracle has DBMS_RANDOM
package that can generate random number. I also found a simple sample code
as follows:

DECLARE
i BINARY_INTEGER;
BEGIN
dbms_random.initialize(19254);
i := dbms_random.random;
INSERT INTO some_table VALUES(i);
dbms_random.terminate;
END;

The problem is when I run the the code, I got some errors like
ORA-06550: line 4, column 5:
PLS-00201: identifier 'DBMS_RANDOM.INITIALIZE' must be declared

I am not a DBA, so I wonder how do I run DBMS_RANDOM package? Anyone has
ideas? Thanks.

Weidong Zhou

Daniel wrote:

> No built in functions that I am aware of.
>
> Daniel
> Austin, Texas
>

fumi

unread,

Jan 9, 2000, 3:00:00 AM1/9/00

to

Weidong Zhou <weidon...@sabre.com> wrote in message news:38767482...@sabre.com...

> Daniel,
> I found something about random number on Internet. Oracle has DBMS_RANDOM
> package that can generate random number. I also found a simple sample code
> as follows:
>
> DECLARE
> i BINARY_INTEGER;
> BEGIN
> dbms_random.initialize(19254);
> i := dbms_random.random;
> INSERT INTO some_table VALUES(i);
> dbms_random.terminate;
> END;
>
> The problem is when I run the the code, I got some errors like
> ORA-06550: line 4, column 5:
> PLS-00201: identifier 'DBMS_RANDOM.INITIALIZE' must be declared
>
> I am not a DBA, so I wonder how do I run DBMS_RANDOM package? Anyone has
> ideas? Thanks.

DBMS_RANDOM package is not created in a default installation.
You may ask your DBA to create it and grant the execution privilege to you.

Rexx

unread,

Jan 14, 2000, 3:00:00 AM1/14/00

to

What script create DBMS_RANDOM package (and other usefull DBMS packages) fro
Oracle 7.3 on NT?

--
*********************************************
Remove nospam from address

neb...@eunet.yu
*****************************************

fumi <fu...@tpts5.seed.net.tw> wrote in message
news:85aa98$1kc$2...@news.seed.net.tw...

Sybrand Bakker

unread,

Jan 14, 2000, 3:00:00 AM1/14/00

to

None. It doesn't exist in 7.3. You can get an alternative solution from the
website of Thomas Kyte,
http://osi.oracle.com/~tkyte

Hth,

--
Sybrand Bakker, Oracle DBA

Rexx <nebacar...@eunet.yu> wrote in message
news:85mva9$hic$1...@SOLAIR2.EUnet.yu...

Sean M. Dillon

unread,

Jan 14, 2000, 3:00:00 AM1/14/00

to

In Oracle 8 (and up...)

> cd $ORACLE_HOME/rdbms/admin
> grep -in DBMS_RANDOM *
dbmsrand.sql:22:CREATE OR REPLACE PACKAGE dbms_random AS
dbmsrand.sql:61:END dbms_random;
dbmsrand.sql:63:CREATE OR REPLACE PACKAGE BODY dbms_random AS
dbmsrand.sql:107:END dbms_random;
dbmsrand.sql:109:DROP PUBLIC SYNONYM dbms_random;
dbmsrand.sql:111:CREATE PUBLIC SYNONYM dbms_random FOR sys.dbms_random;
dbmsrand.sql:113:GRANT EXECUTE ON dbms_random TO public;

SQL> desc DBMS_RANDOM
PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEED BINARY_INTEGER IN
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEED BINARY_INTEGER IN
PROCEDURE TERMINATE

For versions 7.x and before, visit:

http://govt.oracle.com/~tkyte/Misc/Random.html

which explains how to create your own DBMS_RANDOM package in PL/SQL.

hope this helps...

On 07 Jan 2000 17:05:03 EST, "Daniel" <de...@flash.net> wrote:

>No built in functions that I am aware of.
>
>Daniel
>Austin, Texas
>

>"Weidong Zhou" <weidon...@sabre.com> wrote in message

>news:38765E7C...@sabre.com...
>> My PL/SQL procedure needs a RANDOM function like random( 0, 1000 ) to
>> generate random number between 0 and 1000. Anyone knows whether ORACLE
>> has this kind functions? Thanks a lot.
>>
>> Weidong Zhou
>>
>>
>

--
Sean Dillon
Oracle Corporation
sdi...@us.oracle.com
Reston, VA.
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation