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.
05-25-2001, 01:29 PM
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.
05-25-2001, 01:51 PM
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]
05-25-2001, 03:06 PM
I will cook something up and report back with an example (just for you...).
05-25-2001, 04:16 PM
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:
05-25-2001, 04:22 PM
In case you have problems with general usage of DBMS_RANDOM package (seed, initialize), here is a basic example of it's usage:
Oracle8i Enterprise Edition Release 22.214.171.124.0 - Production
With the Partitioning and Java options
PL/SQL Release 126.96.36.199.0 - Production
SQL> SET SERVEROUTPUT ON
3 FOR i IN 1..10 LOOP
5 END LOOP;
PL/SQL procedure successfully completed.
05-26-2001, 02:46 AM
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..
05-27-2001, 05:54 PM
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.
05-28-2001, 02:54 AM
Thanks for your valuable reply...
05-31-2001, 02:50 PM
I guess jmodic did a pretty good job. He/she is amazing.
thanks jmodic and all those who contributed to this thread.