Random value between a range

I would like to randomize the values of x and y in the following query:

insert into tab1 values (chr(x)||chr(y)||);

This would allow me to populate random values of strings in this table. I plan to use several of these random generators (concatenated) to populate several hundred thousand records.

Is there a function in PL/SQL or SQL that would do this ? The Dbms_random does not have a range restriction (am I wrong?). I would need to restrict the range of the x and y to be 65-90 incl for the chr function to return me characters of the english-alphabet.

thanks for your help.

Try using DBMS_RANDOM.VALUE(x,y). Just checkout the exact syntax. This function returns a random number between x and y. Description of DBMS_RANDOM.VALUE is not available in oracle documentation.

Good Luck.

Pinakin

i love it, nobody answers any of the responses to the random questions. the closest you get is 'use DBMS_RANDOM' or 'check out DBMS_RANDOM'. nobody actually says anything on how to use it. does anyone have a simple example?

[Edited by tron on 05-25-2001 at 02:59 PM]

Tron,

I will cook something up and report back with an example (just for you...).

It's true the DBMS_RANDOM package doesn't offer any range-base generator, its RANDOM function only return random *******s in the range of BINARY_INTEGER, which means between -2**31 .. 2**31. But it is very trivial to get only values from a specified range. For example, if you wan't to get random positive integers in the range between 100 and 199 you would use something like:

100+ABS(MOD(dbms_random.random,100))

HTH,

P.S.
In case you have problems with general usage of DBMS_RANDOM package (seed, initialize), here is a basic example of it's usage:

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_RANDOM.INITIALIZE(87241510);
3 FOR i IN 1..10 LOOP
4 DBMS_OUTPUT.PUT_LINE(100+ABS(MOD(DBMS_RANDOM.RANDOM,100)));
5 END LOOP;
6 DBMS_RANDOM.TERMINATE;
7 END;
8 /

177
186
107
116
146
174
162
137
158
145

PL/SQL procedure successfully completed.

Dear jmodic,
I am using Oracle 8.1.6 version...

I tried out your example.... but it gives error as
"" identifier 'DBMS_RANDOM.INITIALIZE' must be declared""
.. so please let me know how to initilize this package..

Thanx in advance..

There might be several reasons why you can't use the package DBMS_RANDOM. Check in the following order:

1. Is package and package body SYS.DBMS_RANDOM installed in your system? AFAIK this package is not instaled by default.

2. If it is installed, is it valid? It might be that some packages on which it depends are not installed or valid (like DBMS_CRYPTO_TOOLKIT).

3. If both 1. and 2. are OK, check your synonyms and privileges on that package.


If the package DBMS_RANDOM is not installed, you have to instal it under schema SYS. You have to run scrypt $ORA_HOME/rdbms/admin/dbmsrand.sql

You might also need to install DBMS_CRYPTO_TOOLKIT (scrypts dbmsoctk.sql and prvtoctk.plb), UTL_RAW (utlraw.sql and prvtrawb.plb) and mybe some others to succesfully compile DBMS_RANDOM.

Hai Friend,
Thanks for your valuable reply...

Tron,

I guess jmodic did a pretty good job. He/she is amazing.

thanks jmodic and all those who contributed to this thread.

mayse.

i am just throwing some more light on random

DECLARE
v_random number;
BEGIN
dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
--
FOR i IN 1 .. 5
LOOP
v_random := dbms_random.value(0,1);
dbms_output.put_line(v_random);
END LOOP;
END;

Vijay,

Your DBMS_RANDOM package is obviously not the package that Oracle supplies in its distribution from 8.0 onward. Yours must be one of those home made PL/SQL random generators that were available on the internet for releases 7.*. Obviously you have named this home made package with the same name oracle names its built-in random generator: DBMS_RANDOM (although I would strongly discourage the usage of the built-in names for user generated objects).

Why the DBMS_RANDOM from your code could not be original SYS.DBMS_RANDOM package?

1.) There is no function DBMS_RANDOM.VALUE in original package

2.) DBMS_RANDOM.SEED procedure accepts only BYNARY_INTEGER parameter, while you parameter is string with nonnumerical characters

3.) You must first initialize the package (supply an initial seed value) with DBMS_RANDOM.SEED procedure before you start using it

yes! jmodic is the dude/dudette. thanks for the example.