Great Help
Pichaimani Balasubramanian, July 06, 2001 - 12:33 pm UTC
Hi Tom,
Thanks a lot. Using 'DDDSSSS' format for generating unique value in versions prior to 8i is a great idea.
Branka, January 02, 2002 - 11:05 am UTC
getting error
Ravi Kumar, July 22, 2002 - 3:13 pm UTC
Hi Tom,
I ran all the scripts that you mentioned in the above link. But I am getting error when using it in sql.
> select dbms_random.random from dual;
select dbms_random.random from dual
*
ERROR at line 1:
ORA-06571: Function RANDOM does not guarantee not to update database
what is the usage of random? I am on Oracle 8.0.5
Thanks,
Ravi.
July 22, 2002 - 6:29 pm UTC
dbms_random wasn't selectable from SQL in 805. It is in 8i. You can use my random package until then.
Still Get Error....
Riaz Shahid, July 23, 2002 - 5:24 am UTC
Hi Tom!
I am using 8.1.5. I tried to select random no but got the following error:
SQL> select dbms_random.random from dual;
select dbms_random.random from dual
*
ERROR at line 1:
ORA-00904: invalid column name
Why it is giving me error?
Regards
July 23, 2002 - 10:34 am UTC
read the link above. As long as you are in 815, you'll get this error. As I said, use the random package written by me available via the link above, it is selectable in SQL in 815
One more Question ....
Riaz Shahid, July 30, 2003 - 9:59 am UTC
Can we generate a random number between m and n (say between 1 and 2) ???
Riaz
July 30, 2003 - 10:25 am UTC
Sorry But.....
Riaz Shahid, July 30, 2003 - 10:37 am UTC
Sorry to say but i couln't find any information regarding my question. Can you please give me an example to how to do that ?
Thanks
July 30, 2003 - 11:22 am UTC
doh, sorry about that -- i just realized the docs are a tad "lacking"
ops$tkyte@ORA920> select text from all_source where name = 'DBMS_RANDOM' and type= 'PACKAGE' order by line;
TEXT
-----------------------------------------------------------------------------------------------------------------------------------
PACKAGE dbms_random AS
------------
-- OVERVIEW
--
-- This package should be installed as SYS. It generates a sequence of
-- random 38-digit Oracle numbers. The expected length of the sequence
-- is about power(10,28), which is hopefully long enough.
--
--------
-- USAGE
--
-- This is a random number generator. Do not use for cryptography.
-- For more options the cryptographic toolkit should be used.
--
-- By default, the package is initialized with the current user
-- name, current time down to the second, and the current session.
--
-- If this package is seeded twice with the same seed, then accessed
-- in the same way, it will produce the same results in both cases.
--
--------
-- EXAMPLES
--
-- To initialize or reset the generator, call the seed procedure as in:
-- execute dbms_random.seed(12345678);
-- or
-- execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
-- To get the random number, simply call the function, e.g.
-- my_random_number BINARY_INTEGER;
-- my_random_number := dbms_random.random;
-- or
-- my_random_real NUMBER;
-- my_random_real := dbms_random.value;
-- To use in SQL statements:
-- select dbms_random.value from dual;
-- insert into a values (dbms_random.value);
-- variable x NUMBER;
-- execute :x := dbms_random.value;
-- update a set a2=a2+1 where a1 < :x;
-- Seed with a binary integer
PROCEDURE seed(val IN BINARY_INTEGER);
PRAGMA restrict_references (seed, WNDS);
-- Seed with a string (up to length 2000)
PROCEDURE seed(val IN VARCHAR2);
PRAGMA restrict_references (seed, WNDS);
-- Get a random 38-digit precision number, 0.0 <= value < 1.0
FUNCTION value RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random Oracle number x, low <= x < high
FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random number from a normal distribution
FUNCTION normal RETURN NUMBER;
PRAGMA restrict_references (normal, WNDS);
-- get a random string
FUNCTION string (opt char, len NUMBER)
/* "opt" specifies that the returned string may contain:
'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters
*/
RETURN VARCHAR2; -- string of <len> characters (max 60)
PRAGMA restrict_references (string, WNDS);
-- Obsolete, just calls seed(val)
PROCEDURE initialize(val IN BINARY_INTEGER);
PRAGMA restrict_references (initialize, WNDS);
-- Obsolete, get integer in ( -power(2,31) <= random < power(2,31) )
FUNCTION random RETURN BINARY_INTEGER;
PRAGMA restrict_references (random, WNDS);
-- Obsolete, does nothing
PROCEDURE terminate;
TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
END dbms_random;
86 rows selected.
so,
ops$tkyte@ORA920> select dbms_random.value(1,2) from all_users;
DBMS_RANDOM.VALUE(1,2)
----------------------
1.19292441
1.66853822
1.04933897
1.16628319
1.0932129
1.14666053
1.712686
1.39888293
......
is what you are looking for.
For Riaz
Paul, July 30, 2003 - 10:51 am UTC
Come on, it's not rocket science
select mod(abs(dbms_random.random),m)+n from dual;
will produce a random number between n and (m+n-1)
e.g. mod(abs(dbms_random.random),4)+3 will produce a random number between 3 and 6.
Try this...
Jose Cleto, July 30, 2003 - 11:03 am UTC
Riaz, ty this one...
Using tom's RANDOM :
Select ( rand * ( Last_Value - First_Value) + 1 ) + First_Value
from dual;
Being First and Last values the ones tha you want to use like lower and higher limits.
Hope it helps....
BTW: Using inline wiews those values could come from a table!!!
For Paul...
Riaz Shahid, July 30, 2003 - 11:07 am UTC
Dear Paul !
Thanks a lot. But i am still having problems. Consider:
1* select mod(abs(dbms_random.random),4)+3 from dua
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
4
Elapsed: 00:00:00.88
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
6
Elapsed: 00:00:00.03
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
4
Elapsed: 00:00:00.88
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
3
Elapsed: 00:00:00.88
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
5
Elapsed: 00:00:00.88
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
5
Elapsed: 00:00:00.87
stax@STARR.LHR> /
MOD(ABS(DBMS_RANDOM.RANDOM),4)
==============================
5
Elapsed: 00:00:00.87
I tried to get a random number between 3 & 6 (and u know there are infinite nos. betweeen 3 & 6 ???). But your code always gives me Whole no.
Riaz
Simple & to the point Answer...
Riaz Shahid, July 30, 2003 - 11:26 am UTC
Thanks a lot tom.
For Riaz Shahid from Pakistan
Prince, July 30, 2003 - 11:27 am UTC
SELECT dbms_random.value(m,n)
FROM dual;
Where m and n could be any number. It works fine brother.
Riaz
Jim, July 30, 2003 - 12:02 pm UTC
The mod function returns just integers. mod is the iteger remainder of a division of 2 numbers. So that is why you are only returning integers. Also since we have limited precision in Oracle, albeit quite good, there are not an infinite set of numbers between 3 and 4. Theoretically there are, but since Oracle is limited to 38 digits of precision there are a limited number of numbers between 3 and 4.
Random number generation with bias
Hector, November 18, 2003 - 5:55 am UTC
Hi Tom
Can you tell me if it is possible to generate random numbers that follow a distribution curve.
(Yeah I know that it wouldn't be truly random)
Let's assume that I have a 1000 row table that has a column that contains 50 distinct values 1 - 50
Using the old mod(rownum,<num>) trick, I can easily get an even spread of data into the column
Example
-------
create table my_table(
my_number number(2));
insert into mytable (
select mod(rownum,50) + 1
from all_objects
where rownum <= 1000);
This creates 20 occurrences of each value.
How can I manufacture the data so that I can pick a specific value that would be at the peak of a distribution curve.
Say for example that I picked the value 30, the next most popular values should then be 29 and 31, then 28 and 32 etc.
Also, how could I vary the extremes of the data
One time I might want (roughly of course)
the value 30 occurrs 500 times
29 and 31 60 times
28 and 32 45 times
etc
and the next time I might want
the value 30 occurrs 50 times
29 and 31 47 times
28 and 32 43 times
etc
(9.2 by the way)
November 21, 2003 - 7:45 am UTC
dbms_random.normal will return a "random" "normally distributed" number between -1 and +1 (most at 0).
You can use this to generate that set in a normal distribution.
but to get the exact proportions you want -- don't know of anything "out of the box" -- to get that level of control.
Wrong range?
Michal, March 07, 2004 - 12:57 pm UTC
<quote>
dbms_random.normal will return a "random" "normally distributed" number between -1 and +1 (most at 0).
<quote>
Try this:
DECLARE
x BINARY_INTEGER;
BEGIN
x := 123456;
-- Seed (overloading BINARY_INTEGER or CHAR)
DBMS_RANDOM.SEED(x);
FOR x IN 1 .. 100 LOOP
DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.NORMAL);
END LOOP;
END;
/
and you'll see:
.6552453068736144004189012503457494380011
-.1998072752961617437697821628544956536385
.3616098566482172556647023002713899797728
-.76374538378660583258238903668412171048
2.26313928540300668848441957444690328863
.0987309681945767405833233098422463301788
2.74257619226505398219534872384171306092
-.2652023947009487738914447653006839953976
.3469172911120469484388168373254850902541
1.21220943182047716230612464378150888317
-.5507118148915647513893381978398223287084
1.35837102234415831658204538872406601983
-.9015120693203381537942783928117923860256
.7446059610015514456119755613915469050156
.7171427438288355010869838787707389810995
-.7340478738237179598845112752918758516098
.3411346277117262435118046913527693254062
.1432293523333120672398100110338354864665
-1.32875907830606105786071613261960764497
-1.59036719813875108820221952610750594883
2.51443321535476818233522333506466990204
...
So I doubt that the range you have mentioned (-1,+1) is right.
??
March 07, 2004 - 1:10 pm UTC
sorry you are correct -- the +/- 1 was for the stddev of the normal distribution (most of the data will fall within +/- 1 of 0
ops$tkyte@ORA9IR2> create table t
2 as
3 select dbms_random.normal from all_objects;
Table created.
ops$tkyte@ORA9IR2> select avg(normal), stddev(normal) from t;
AVG(NORMAL) STDDEV(NORMAL)
----------- --------------
-.00250014 1.00260327
ops$tkyte@ORA9IR2> edit test
ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t
2 as
3 select dbms_random.normal from all_objects;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select avg(normal), min(normal), max(normal), stddev(normal) from t;
AVG(NORMAL) MIN(NORMAL) MAX(NORMAL) STDDEV(NORMAL)
----------- ----------- ----------- --------------
.001419749 -3.9844695 4.15448314 .999318265
rabeila, March 07, 2004 - 3:23 pm UTC
Just checking ...
Greg, January 28, 2005 - 9:38 am UTC
I'm probably blind, but I didn't see anything in the docs explicitly mentioning this case .. however ...
floor ( dbms_random.value ( 1, 10 ) ) ... to generate random whole numbers between 1 and 10, right??
well, not quite ... the results are values:
1,2,3,4,5,6,7,8 and 9 ... 10 never seems to show up ...
I've run some programs to double check, and they don't ..
Now, I suspect this is as it should be, because the underlying .value method is generating a range from 1.00000000 ... to 1.99999999 ... so no sweat, I can change the routine to: floor ( dbms_random.value(1,11) ) ...
All I wanted to do is confirm is this is correct or not - or whether there is a small chance of that 10 ever occurring??
(from my tests - it doesn't seem to be .. but I understand odds just enough not to rely on that ... )
Thanks!!
January 28, 2005 - 2:25 pm UTC
-- Get a random 38-digit precision number, 0.0 <= value < 1.0
FUNCTION value RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random Oracle number x, low <= x < high
FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
ops$tkyte@ORA9IR2> select text from all_source
2 where name = 'DBMS_RANDOM'
3 and type = 'PACKAGE'
4 order by line;
TEXT
-------------------------------------------------------------------------------
PACKAGE dbms_random AS
------------
-- OVERVIEW
--
-- This package should be installed as SYS. It generates a sequence of
-- random 38-digit Oracle numbers. The expected length of the sequence
-- is about power(10,28), which is hopefully long enough.
--
--------
-- USAGE
--
-- This is a random number generator. Do not use for cryptography.
-- For more options the cryptographic toolkit should be used.
--
-- By default, the package is initialized with the current user
-- name, current time down to the second, and the current session.
--
-- If this package is seeded twice with the same seed, then accessed
-- in the same way, it will produce the same results in both cases.
--
--------
-- EXAMPLES
--
-- To initialize or reset the generator, call the seed procedure as in:
-- execute dbms_random.seed(12345678);
-- or
-- execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
-- To get the random number, simply call the function, e.g.
-- my_random_number BINARY_INTEGER;
-- my_random_number := dbms_random.random;
-- or
-- my_random_real NUMBER;
-- my_random_real := dbms_random.value;
-- To use in SQL statements:
-- select dbms_random.value from dual;
-- insert into a values (dbms_random.value);
-- variable x NUMBER;
-- execute :x := dbms_random.value;
-- update a set a2=a2+1 where a1 < :x;
-- Seed with a binary integer
PROCEDURE seed(val IN BINARY_INTEGER);
PRAGMA restrict_references (seed, WNDS);
-- Seed with a string (up to length 2000)
PROCEDURE seed(val IN VARCHAR2);
PRAGMA restrict_references (seed, WNDS);
-- Get a random 38-digit precision number, 0.0 <= value < 1.0
FUNCTION value RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random Oracle number x, low <= x < high
FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (value, WNDS);
-- get a random number from a normal distribution
FUNCTION normal RETURN NUMBER;
PRAGMA restrict_references (normal, WNDS);
-- get a random string
FUNCTION string (opt char, len NUMBER)
/* "opt" specifies that the returned string may contain:
'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters
*/
RETURN VARCHAR2; -- string of <len> characters (max 60)
PRAGMA restrict_references (string, WNDS);
-- Obsolete, just calls seed(val)
PROCEDURE initialize(val IN BINARY_INTEGER);
PRAGMA restrict_references (initialize, WNDS);
-- Obsolete, get integer in ( -power(2,31) <= random < power(2,31) )
FUNCTION random RETURN BINARY_INTEGER;
PRAGMA restrict_references (random, WNDS);
-- Obsolete, does nothing
PROCEDURE terminate;
TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
END dbms_random;
86 rows selected.
Yep .. I'm blind ... :\
Greg, February 03, 2005 - 9:40 am UTC
Thanks!!
( Gawd I feel dumb sometimes ... *sigh* )
Alpha -numeric sequence
mary W, March 02, 2005 - 9:25 am UTC
Tom,
Is there a way to create an alpha-numeric sequence to be used in place of user id? For about 10-20 million records?
March 02, 2005 - 10:07 am UTC
need more detail, technically
create sequence s;
does that -- true, just has numbers but it fits the formal defintion of alpha-numeric.
(so you must have some "template" you want these created using?)
alpha-numeric sequence
mary w, March 02, 2005 - 2:19 pm UTC
i would like the sequences to be 6 to 8 characters long and include both numbers and letters and be unique.
and now i am thinking the sequence should have ability to accomodate 100 million ids.
there is no set format really so it could look like
A1002XA
A1089TR
March 02, 2005 - 5:04 pm UTC
ops$tkyte@ORA9IR2> create sequence s maxvalue 4294967296 start with 4294967295 increment by -1;
Sequence created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_char(s.nextval,'fm0000000X' ) from all_users;
TO_CHAR(S
---------
FFFFFFFF
FFFFFFFE
FFFFFFFD
FFFFFFFC
FFFFFFFB
FFFFFFFA
FFFFFFF9
FFFFFFF8
FFFFFFF7
FFFFFFF6
FFFFFFF5
FFFFFFF4
FFFFFFF3
FFFFFFF2
FFFFFFF1
FFFFFFF0
FFFFFFEF
FFFFFFEE
FFFFFFED
FFFFFFEC
FFFFFFEB
FFFFFFEA
FFFFFFE9
FFFFFFE8
FFFFFFE7
25 rows selected.
starting with 100,000,000 gives more interesting start numbers -- but this fits your specification.
you could start with 1 and go up, but the starting numbers were boring to look at :)
Alpha numeric sequence
mary W, March 03, 2005 - 12:07 pm UTC
Thank you,
I need those sequences to look a bit more user friendly since that is what i will be returning to the user as a confiramtion code.
I used dbms_random.string('x',6) to genrerate about 24 million alpha-numeric id's. They look pretty good to me. See below. The question is since they are random how often do they repeat? Should i generate a whole bunch of unique ids and insert them into support table which i then can use to insert into user table with a unique constraint? or is there way i can generate these types of unique ids on the fly?
1OAWPC
72JFCD
7L1L2B
7LZT4U
7MINQU
BLXVEU
GAJWD7
P6OOVK
UTGN0U
X3EJTR
March 03, 2005 - 12:13 pm UTC
how are those more user friendly then a hex number?
dbms_random is a random thing, it'll tend to generate the same codes at some time. you could
create table assigned_ids ( x varchar2(6) primary key ) organization index;
and as you generate one, insert it in there. on dup val on index, do it again.
dbms_random package
Jason Clements, December 09, 2005 - 9:57 am UTC
I never knew about this - I'd always thought it odd that Oracle didn't include an equivalent to RAND().
This really got me out of a hole - Thanks !!
Jason
unique from random
Nikunj, January 06, 2006 - 8:28 am UTC
Dear Sir,
I need unique no from dbms.random(100,200) every numbers should be unique between the list.
regards,
nikunj
January 06, 2006 - 2:01 pm UTC
that is technically impossible, since Oracle numbers are 38 digits long - that is not an infinite set of numbers.
what are you really trying to do here?
more on unique no.
Nikunj, January 07, 2006 - 12:26 am UTC
Followup:
that is technically impossible, since Oracle numbers are 38 digits long - that
is not an infinite set of numbers.
what are you really trying to do here?
I am trying to do as below.
My user want to enter starting no., ending no. and list of random no. wants between starting no. and ending no.
i.e. starting no. 200 and ending no. 400 he wants 50 random no. from list.
so i had created loop which will select random no. between the range and base on user input on how much random no. he wants.
I had try but i am getting non unique nos.
January 07, 2006 - 10:14 am UTC
so, really what you want to do is
a) generate the set of all integers between "lo" and "hi"
b) get a random selection from that
no problem when stated like that - when stated the way you did the first time - big problem!
Huge difference between stating what the goal is (then people can give solutions) versus offering a partial solution and saying "finish it" :)
ops$tkyte@ORA10GR2> variable lo number
ops$tkyte@ORA10GR2> variable hi number
ops$tkyte@ORA10GR2> variable num_to_get number
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :lo := 200
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> exec :hi := 400
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> exec :num_to_get := 5;
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> with data
2 as
3 (select level+:lo-1 l
4 from dual
5 connect by level <= (:hi-:lo+1)
6 )
7 select *
8 from (select * from data order by dbms_random.random)
9 where rownum <= :num_to_get;
L
----------
221
297
367
305
333
ops$tkyte@ORA10GR2> /
L
----------
287
344
332
315
277
ops$tkyte@ORA10GR2> /
L
----------
213
278
209
357
341
ops$tkyte@ORA10GR2> /
L
----------
347
397
249
295
354
That is but one approach.
Initializing DBMS_RANDOM
A reader, January 12, 2006 - 7:27 am UTC
Oracle documentation states:
DBMS_RANDOM must be initialized before calling the random number generator. The generator produces 8-digit integers. If the initialization subprogram is not called, then the package raises an exception.
What does this mean?
I've created a package that uses DBMS_RANDOM.value and have never had a problem. Now some "software architect" guy says my package should first initialize the random number generator.
January 12, 2006 - 11:03 am UTC
the doc is out of date, it doesn't need to be seeded in current releases. it is "self seeding"
question on dbms_random.value() function
Senthil Ramanujam, April 16, 2006 - 9:44 am UTC
Tom,
Here is a test case for my question below.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
SQL> drop table t;
Table dropped.
SQL> create table t as select rownum rnum, rpad('a', 100) pad1 from all_objects where rownum <= 1000;
Table created.
SQL> alter table t add constraint t_pk primary key(rnum) using index (create index t_pk on t(rnum));
Table altered.
SQL> select min(rnum), max(rnum) from t;
MIN(RNUM) MAX(RNUM)
---------- ----------
1 1000
1 row selected.
SQL> select count(*) from t where rnum = trunc(dbms_random.value(1,1000));
COUNT(*)
----------
137
1 row selected.
What value does dbms_random.value generate to produce this output? I am sure I am missing something here. Is there an explanation for this behavior or is it a hidden feature?
thanks.
April 16, 2006 - 5:47 pm UTC
I'll bug that - that isn't right. There is something wrong there starting in 10gr2.
thanks
Tests
Michel Cadot, April 17, 2006 - 2:46 am UTC
I changed a little bit the test case (after testing the previous one) with the following function:
create or replace function f return number
is
begin
dbms_application_info.set_client_info(userenv('client_info')+1 );
return trunc(dbms_random.value(1,1000));
end;
/
and check:
exec dbms_application_info.set_client_info(0);
select count(*) from t where rnum = f;
select userenv('client_info') from dual;
All tests was made on Windows XP.
With 8.1.7.4, function is called 1000 times and i get a count of 0 to 3, more often 0, less 1 and so on. This was expected, i have 1/1000 likelyhood to get the correct number.
With 9.2.0.6 and 10.2.0.1, f is called 2 times and i get any count but more often 0, about 1 on 3.
Regards
Michel
April 17, 2006 - 8:01 am UTC
it is definitely "bugged" on 10gr2
ops$tkyte@ORA10GR2> @test
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Table dropped.
Table created.
Table altered.
MIN(RNUM) MAX(RNUM)
---------- ----------
1 1000
COUNT(*)
----------
198
I say it is "bugged" because if you change the count(*) to rnum - you get some "interesting" results.
Tests - followup
Michel Cadot, April 17, 2006 - 9:24 am UTC
I understand and agree this is a bug.
I tried "select rnum ..." and get various number of rows with almost (but not all) the same value but not only in 10.2, also in 9.2 and even SQL*Plus seems to receive something strange as it displays rnum sometimes as an integer value sometimes as a floating point number with all 0 decimals.
(9.2.0.6 DB and 10.2.0.1 SQL*Plus)
SQL> select rnum from t where rnum = trunc(dbms_random.value(1,1000));
no rows selected
SQL> /
RNUM
----------
48
48
48
48
48
48
...
48.00
48.00
48.00
48.00
48.00
48.00
48.00
48.00
48.00
...
48.00
48.00
48.00
48
48.00
48.00
48.00
48.00
...
48.00
48.00
273 rows selected.
Regards
Michel
April 17, 2006 - 10:00 am UTC
that would be why I called the results "interesting". filing the bug...
SYS_GUID numbers
Jason, April 29, 2006 - 9:44 am UTC
Tom:
1. Can we assume it is safe to use a generated number from SYS_GUID as a session id and store it in a table for web state management?
According to oracle docs they generate the number based on host value, process value, and non-repeating value. For web users using one proxy server the first two values might be the same. It looks the third value will never be repeated.
In 8i, we did a test and we can easily identify the next value coming.
1276965BDA8C3F6CE0438C93F11D3F6C
1276965BDA8D3F6CE0438C93F11D3F6C
1276965BDA8E3F6CE0438C93F11D3F6C
2. Would storing that number (unencrypted) in a database table (sessionid,parameter,value) make it vulnerable and unsecure for other developers, ad-hoc users, etc? Basically by stealing it and creating a cookie with that number and faking a user session in mod_plsql app.
thanks,
April 30, 2006 - 4:55 am UTC
1) yes, they are supposed to be globally unique identifiers.
2) could be, yes. but the use of appropriate access control and https would offset that.
SYS GUIID
Jason, April 30, 2006 - 10:45 pm UTC
Tom:
1. It is unique, but do you agree it is very unsafe and can be easily predicted in 8i based on the experiment of generated numbers I listed.
2. I do not see how https would have to do with that. https is more about encrypting the message going over the internet. I can predict a the session id or look it up from session table, create a cookie for that value on my machine and submit it over https and I have a valid session as another user.
May 01, 2006 - 2:07 am UTC
1) it depends entirely on whether it matters to you or not. if that matters to you, don't use it, build something yourself.
2) https protects the cookie (with the sessionid). ACCESS CONTROL on the server protects data.
if the cookie is protected IN MOTION (on net) and
the cookie is protected AT REST (in table)
then
I don't see the problem
end if
SUS GUID
Jason, April 30, 2006 - 11:51 pm UTC
Tom:
I am not sure about replicating the cookie value, because the SYS GUID is a RAW number and SQL*plus is only showing it in HEX. I think the actual number stored in the cookie may be the binary. Correct me if I am wrong, a smart user would not be able to look at a session table storing hex values and recreating a cookie with binary value from that?
May 01, 2006 - 2:08 am UTC
the value stored in the cookie would have to be the HEX STRING since cookies are text.
you would be passing a string of hex characters back and forth
dbms_random.xxx
john sisson, May 02, 2006 - 1:39 pm UTC
Thank you
A succinct intro to the random generator; allowed me to set random linkages in random quantities from part of a test table of 'ads' to a test table of 'caddies'
-- == load some more caddies - into 33% of the ads
update ad set ad.caddy_number = 2500 + trunc(250 * dbms_random.normal)
where ad.ad_number > 5000
and (ad.ad_number + trunc(dbms_random.value(0.00, 2.99))) = ad.ad_number;
Selecting random rows from a table
mohannad, July 12, 2006 - 3:26 am UTC
Dear tom
i have a table named questions, and i want to select in each exam 100 random records, i have read a lot about this issue and i came up that there is three way to do so , but i could not know which one is better is performance
1. select * from questions order by dbms_random.random;
2.select * from question sample (10);
3.use Ora_hash function;
could you advice?
July 12, 2006 - 4:26 pm UTC
#2 would be the most performant - however, that gets a random 10% sample - not exactly 100 rows. And if the table is large (so 10% was say 10,000 records), it would tend to always get a random set from the "front" (since you would just fetch the first 100 rows right...)
How big is the table in question.
With Data
Su Baba, October 09, 2006 - 12:38 pm UTC
Is there advantage in using
variable lo number
variable hi number
variable num_to_get number
exec :lo := 200
exec :hi := 400
exec :num_to_get := 5;
with data
as
(select level + :lo-1 l
from dual
connect by level <= (:hi-:lo+1)
)
select *
from (select * from data order by dbms_random.value)
where rownum <= :num_to_get;
rather than
SELECT *
FROM (
select level + :lo - 1 l
from dual
connect by level <= (:hi - :lo +1)
ORDER BY dbms_random.value
)
WHERE rownum <= :num_to_get;
October 09, 2006 - 1:44 pm UTC
benchmark them, what do YOU observe?
Random Numbers
Curtis, December 19, 2006 - 9:17 am UTC
In response to April 17 post on version 10.2 using dbms_random.value(1,1000)
MIN(RNUM) MAX(RNUM)
---------- ----------
1 1000
select count(*) from t where rnum = trunc(dbms_random.value(1,1000));
COUNT(*)
----------
198
I'm not sure what number was expected in the count...
The reason the count is 198 is because you may have 10 values that = 12 and the another 20 values that = 25 from the random number field. The truncate of Random number is not going to generate 1,2,3,4,5 etc... So some of the numbers between 1 and 1000 are going to be missing.
The below simple query will illustrate the point.
select rnum,trunc(dbms_random.value(1,1000)) from t
order by 2
Thanks,
random vs unique
radino, April 20, 2007 - 5:06 pm UTC
"Randomly generate and unique is sort of an oxymoron."
not allways, for specific requirements there is effective algorithm.. time and space = O(C), where C is cardinality of
of set: <min, max> intersect N
For example:
We want 100 integers from set <1000, 2000> intersect N
pseudocode:
max := 2000;
min := 1000;
a array[max-min];
i int;
for i in 0 .. a.length -1 loop
a[i] := 1000 + i;
end loop;
i = a.length - 1;
to_generate := 100;
while (to_generate > 0 and i > 0) loop
-- random generates integers 0..i-1
random_index := random(i);
print(a[random_index]);
a[random_index] := a[i];
i := i - 1;
to_generate := to_generate - 1;
end loop;
April 20, 2007 - 5:57 pm UTC
but they want them as singletons, mixing up a result set - I can do that easier with just sql.
but dole them out to a large population - concurrently, that is an entirely different problem.
NUMBER GENERATION
Zpatel, April 24, 2007 - 3:26 am UTC
hi tom,
i want to generate no this way...
aa00
aa01
.
.
aa99
ab00
ab01
.
.
zz99
how can i do this?
April 24, 2007 - 11:03 am UTC
not using a sequence easily, so I would abandon it as a design concept all together.
besides, it only gives you:
ops$tkyte%ORA10GR2> select 26*26*10*10 from dual;
26*26*10*10
-----------
6760067,600 id's - insufficient for real life.
Random number
Karthick, October 09, 2007 - 9:04 am UTC
I need to generate 100 random number that satisfies the following 4 conditions
1. Random number must be in the range of 1 to 100
2. All the 100 numbers must be unique.
3. 1st generated number cant have value as 1. 2nd generated number cant have value as 2. 3rd generated number cant have value as 3 and so on...
4. The random number must be a whole number. Decimals not allowed.
Please let me know if it is possible.
session number
jason, October 19, 2007 - 12:19 am UTC
TOm:
Do you recommend using SYS_GUID() number for session id or doing sequence_name.nextval || to_char( sysdate, 'DDDSSSSS' ) or none.
Would not a 32 hex number be too long for session management?
session id
A reader, January 23, 2008 - 7:00 pm UTC
Tom:
what would you use for web session id to generate from db.
would sys_guid be long/safe or not?
noitice how only 1 byte is changing in 9i on sequential calls.
1* select sys_guid() from dual
SQL> /
SYS_GUID()
--------------------------------
446D948F290B505EE0438C93F115505E
1 row selected.
SYS_GUID()
--------------------------------
446D948F290C505EE0438C93F115505E
1 row selected.
SYS_GUID()
--------------------------------
446D948F290D505EE0438C93F115505E
1 row selected.
January 23, 2008 - 8:37 pm UTC
if you care about the sequential-ness of that, re-read the original answer way up there....
my session id is a function of a sequence, a random number and what time it is.
session
A reader, January 23, 2008 - 11:19 pm UTC
it seems that number generated by sys_guid is sequential
You mean you use this for session id
select sequence_name.nextval || to_char( sysdate, 'DDDSSSSS' ) from dual:
do you use a 32 byte session id usually and would the above give you that
January 24, 2008 - 7:41 am UTC
look at my URL, using APEX we get a sessionid that is just a really big number - I'm using oracle so I'm limited to 38 digits, my current session id was:
ops$tkyte%ORA10GR2> select power(2,32), 2720870081534243 from dual;
POWER(2,32) 2720870081534243
----------- ----------------
4294967296 2.7209E+15
somewhat larger than 2^32.
and as stated my_session_id = function( sequence, time, random number )
you may use whatever you LIKE
A reader, January 24, 2008 - 11:16 am UTC
TOm:
Can i use
SYS_GUID()
--------------------------------
446D948F290D505EE0438C93F115505E
or do i have to convert to a numeric number for session id.
any advantages? or same thing
January 24, 2008 - 11:26 am UTC
you can use whatever you like, you have the requirements in your head.
the only firm requirement that we all share for a sessionid would be:
a) it should uniquely identify a session
sys_guid() does that
so does a sequence.
other people might have other requirements - a typical one is:
b) session ids should not be easily guessable
that was one I had for myself, that is why I did what I do. You might not care, in which case - you use sys_guid(), sequence, to_char(timestamp,'someformat'), whatever you want.
session id
A reader, January 24, 2008 - 4:26 pm UTC
Makes a lot of sense. excellent answer
session id
A reader, January 30, 2008 - 9:53 pm UTC
Tom:
Do you usually require the client to send the session id and userid?
If I have a table, and I create a session id for a user. I think i would require the page to send both parameters and check those against the table. correct.
users
-------
userid
password
session_id
expiration_time
February 04, 2008 - 2:53 pm UTC
why?
Sequential GUIDS
Vidar, February 28, 2008 - 2:46 am UTC
Just a comment about sequential generated GUID's. This will vary across platforms, but they are still generated to be theoretically unique, so we use them (stored as raw(16)) and are introducing them as replacements for the old number(10) keys.
Consider the following GUID's generated on our HP-UX-system:
47240CCD04811249E0440017A4ABB0F5
47240CCD04821249E0440017A4ABB0F5
47240CCD04831249E0440017A4ABB0F5
47240CCD04841249E0440017A4ABB0F5
47240CCD04851249E0440017A4ABB0F5
47240CCD04861249E0440017A4ABB0F5
47240CCD04871249E0440017A4ABB0F5
47240CCD04881249E0440017A4ABB0F5
47240CCD04891249E0440017A4ABB0F5
47240CCD048A1249E0440017A4ABB0F5
47240CCD048B1249E0440017A4ABB0F5
vs the same code run on a Win32 system:
A3267C9E051B4156AA1CDBF166F83EB0
D1327DFCD6534B5090CB54AA82211DE1
E6996F441AAE45ABB20C3571DA8A5725
79CBA39CEB214072B3D0038CD5D3ED58
530A7948B4F747558C7B88C406A58788
F39C0BFB65424BD8830FEC282A85543E
E719920768CD474DAFA5A4E3625B52EB
3241619CBDF3410B8ADA2A72CB4C004F
3114438676EE474CA94E20E31E3722D2
0CEE2FA7F88346A6B0F6B8198E117DFB
92471A03C47249EDAB6AC75BCFAA29E7
I don't know why this is happening, whether it's due to some internal Oracle-coding or an OS-function.
OK
Kumar, June 23, 2011 - 5:05 am UTC
Hi Tom,
Suppose if I do
select sys_guid from emp;
I want each row to have an unique value for GUID.
Any way to achieve this?
June 23, 2011 - 8:11 am UTC
ops$tkyte%ORA11GR2> select sys_guid(), empno from scott.emp;
SYS_GUID() EMPNO
-------------------------------- ----------
A660C9B7AB68CEC8E040007F01002565 7369
A660C9B7AB69CEC8E040007F01002565 7499
A660C9B7AB6ACEC8E040007F01002565 7521
A660C9B7AB6BCEC8E040007F01002565 7566
A660C9B7AB6CCEC8E040007F01002565 7654
A660C9B7AB6DCEC8E040007F01002565 7698
A660C9B7AB6ECEC8E040007F01002565 7782
A660C9B7AB6FCEC8E040007F01002565 7788
A660C9B7AB70CEC8E040007F01002565 7839
A660C9B7AB71CEC8E040007F01002565 7844
A660C9B7AB72CEC8E040007F01002565 7876
A660C9B7AB73CEC8E040007F01002565 7900
A660C9B7AB74CEC8E040007F01002565 7902
A660C9B7AB75CEC8E040007F01002565 7934
14 rows selected.
are you seeing something other than what I see - they are already unique?
OK
Kumar, June 23, 2011 - 8:37 am UTC
Hi Tom,
But the value A660C9B7AB68CEC8E040007F01002565 repeats for every row. I want the sys_guid value to be different for each row.
Thanks
June 23, 2011 - 9:28 am UTC
it does not, read it a bit closer
Kumar, June 23, 2011 - 8:38 am UTC
Sorry. My bad. It is unique. Thanks
SYS_GUID Format ?
Karthik, March 27, 2012 - 11:16 am UTC
What is the internal representation/implementation of SYS_GUID?
How is it formated? (for example rowId=file_id||block_ID||obj_ID||rowID)
March 28, 2012 - 8:28 am UTC
it is a raw field that is to be globally unique. beyond that, there is nothing you can discern from it.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions187.htm#SQLRF06120 On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.that initial lead in bit - ON MOST - tells you "do not depend on anything here, other than it is unique"
Ranjan, March 08, 2013 - 2:03 am UTC
Hi Tom,
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table magic as select level a from dual connect by level<11;
Table created.
SQL> select * from magic order by dbms_random.value;
A
----------
8
1
7
6
10
9
2
4
5
3
10 rows selected.
SQL> select * from magic order by dbms_random.value;
A
----------
9
8
10
2
3
4
1
6
7
5
10 rows selected.
SQL> select a from magic order by (select dbms_random.value from dual);
A
----------
1
2
3
4
10
6
7
8
9
5
10 rows selected.
SQL> select a from magic order by (select dbms_random.value from dual);
A
----------
1
2
3
4
10
6
7
8
9
5
10 rows selected.
###############
could you please tell why in case of second query(order by select ...) didn't give random for each run like the first query.
Regards,
Ranjan.
March 11, 2013 - 8:09 am UTC
because you ordered by a constant.
You assume that the subquery would be executed once per row. It doesn't have to.
Anytime you call a statement level non-deterministic function from SQL (a function that is not deterministic for the duration of the sql statement - sysdate is not deterministic but it IS statement level deterministic for example) - you are risking "changing behavior based on the plan".
You have experienced a feature we call scalar subquery caching. Your subquery we executed once - and its value was used over and over. Your second query was functionality equivalent to:
:x := dbms_random.random;
select * from t order by :x;
see
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html for more details
got it ,thanks
Ranjan, March 11, 2013 - 8:42 am UTC
@Ranjan
Sokrates, March 11, 2013 - 9:32 am UTC
...could you please tell why in case ... didn't give random for each run like the first query.
I suppose, you wanted to say
... didn't give random order for each run like the first query ... ?
Please note, that it is just an implementation detail of your Oracle's version, that your first query
... order by dbms_random.value
resulted in the output randomly ordered.
This behaviour could change in the future, Oracle is free to evaluate dbms_random.value just once ( or not at all ) for a query like that.
see
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3181424400346795479#3193419700346287323
@sokrates
Ranjan., March 12, 2013 - 2:05 am UTC
Thans for giving comments.
yes I meant that.(random order)