Solved

How to create random number using oracle ?

Posted on 2007-11-22
6
5,056 Views
Last Modified: 2013-12-18
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
Comment
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
  • Learn & ask questions
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20333280
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 100 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

by:Naveen Kumar
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 25 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

by:KG1973
ID: 31410527
Perfect solution.
0
 
LVL 1

Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question