-
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,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
dbms_random package
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..
RP Kumar
You Can Win, if u believe Yourself
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks...
Hai Friend,
Thanks for your valuable reply...
RP Kumar
You Can Win, if u believe Yourself
-
Tron,
I guess jmodic did a pretty good job. He/she is amazing.
thanks jmodic and all those who contributed to this thread.
mayse.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|