# random number generate on oracle

hi,
i want to generate random number on oracle.
we can generate random numbers by using dbms_random.random function.
and that works on sql/plus but it fails on developer/2000
how can we use this function on developer/2000
is there any another way to generate random numbers?

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Write a stored function which will call dbms_random and return the value generated then call the stored function from within developer. This will work.
0
Commented:
You have to create the function and store it in the database for this to work (Stored function).
0
Author Commented:
i want to a function which is generate a random function.
because dbms.random isnt working on developer/2000
0
Author Commented:
i want to a function which is generate a random number which is 8 and 6 digit.
because dbms.random isnt working on developer/2000
0
Commented:

What is the range of random numbers that you want to generate ?
0
Author Commented:
the range of random number is 100000-999999 and
10000000-99999999
0
Commented:
FUNCTION random_number RETURN NUMBER IS
sec     NUMBER := TO_CHAR(SYSDATE,'SS');
minm  NUMBER := TO_CHAR(SYSDATE,'MI');
hour   NUMBER := TO_CHAR(SYSDATE,'HH24');
mon  NUMBER := TO_CHAR(SYSDATE,'MM');
dy      NUMBER := TO_CHAR(SYSDATE,'D');
rand  NUMBER;
x NUMBER;
BEGIN
x := mod(sec,4);
if x = 0 then
rand := 9*sec + 99*minm +999*hour +9999*mon +99999*dy;
elsif x = 1 then
rand := 99*sec + 999*minm +9999*hour +99*mon +99999*dy;
elsif x = 2 then
rand := 779*sec + 77*minm +7777*hour +93*mon +99999*dy;
else
rand := 9999*sec + 888*minm +7777*hour +97*mon +99999*dy;
end if;
RETURN rand;
END;
/

This is the small example which generates the 6 digit random number.
There are many ways to generate it.

Hope this helps  you to analyze and implement it
0

Experts Exchange Solution brought to you by