Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)

  • From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>, "'oracle-l-bounce@xxxxxxxxxxxxx'" <oracle-l-bounce@xxxxxxxxxxxxx>
  • Date: Wed, 28 Apr 2010 09:40:19 -0500

(First one didn't seem to go through to the list - resending as plain txt)

DB Version:
Server CPU24 Windows 64Bit
Client: CPU24 Windows 32bit

I haven't researched this yet, but I'm curious why this might be happening.

Here's the test setup:

create table test1
(val1 number,
val2 varchar2(50)
Table Created

v1 number := 1;
v_text varchar2(26);
for i in 1..500
select substr('abcdefghijklmnopqrstuvwxyz',1,dbms_random.value(1,26))
into v_text
from dual;
insert into test1
(val1, val2)
(v1, v_text);
v1 := v1+1;
end loop;
500 Rows Inserted


Here is the interesting part (to me) - Sometimes it returns 1 row, sometimes it 
returns more than 1 row, and sometimes it returns no rows.
To my mind, "trunc(dbms_random.value(1,500)" should return a random value 
between 1 & 500 every time and just one value.  Is this weird, or is it just 

SQL> select * from test1
  2  where val1 = trunc(dbms_random.value(1,500))
  3  /

      VAL1 VAL2
---------- --------------------------------------------------
       307 abcde

SQL> /

no rows selected (?!)

SQL> /

      VAL1 VAL2
---------- --------------------------------------------------
         2 abcdefghijklmnopqrstuv

SQL> /

no rows selected

SQL> / (?!)

      VAL1 VAL2
---------- --------------------------------------------------
        87 abcdefghijklmnopqrs
       259 abcde



Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.


Other related posts: