Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# How to create random number using oracle ?

Posted on 2007-11-22
Medium Priority
5,076 Views
I want to create a random number between 1 to n and return any number within the range using sql. Our database is oracle 10g.
0
Question by:KG1973
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 28

Expert Comment

ID: 20333280
0

LVL 27

Accepted Solution

sujith80 earned 400 total points
ID: 20333283
Use the dbms_random package.

E.g.
SQL> select round(dbms_random.value(1, 100)) from dual;

ROUND(DBMS_RANDOM.VALUE(1,100))
-------------------------------
68

SQL> select round(dbms_random.value(1, 100)) from dual;

ROUND(DBMS_RANDOM.VALUE(1,100))
-------------------------------
31
0

LVL 28

Expert Comment

ID: 20333290
with the below code, i am able to generate 10 random numbers between 1 and 10

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_seed  BINARY_INTEGER;
BEGIN
l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
DBMS_RANDOM.initialize (val => l_seed);
FOR cur_rec IN 1 ..10 LOOP
DBMS_OUTPUT.put_line('value(low => 1, high => 10): ' || round(to_number(TO_CHAR(DBMS_RANDOM.value(low => 1, high => 10)))));
END LOOP;
DBMS_RANDOM.terminate;
END;
/

Thanks
0

LVL 32

Assisted Solution

awking00 earned 100 total points
ID: 20340423
You can create a function -
create or replace function rand(n_in in number)
return number is
ret_n  number;
begin
ret_n := round(dbms_random.value(1,n_in));
return ret_n;
end;
/
Then input value for n
For example,
SQL> select rand(80) from dual;
RAND(80)
----------
60
SQL> /
RAND(80)
----------
36
,etc.
0

LVL 1

Author Closing Comment

ID: 31410527
Perfect solution.
0

LVL 1

Author Comment

ID: 20347505
You just solved my problem. Thank you very much.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For manyâ€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month9 days, 5 hours left to enroll