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 10.2.0.4 CPU24 Windows 64Bit
Client:
10.2.0.4 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

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

Commit
/

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 
me???


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

SQL>

Thoughts?

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.

--
//www.freelists.org/webpage/oracle-l


Other related posts: