Problem solve Get help with specific problems with your technologies, process and projects.

Random number between two integers

This function generates a random number between any given two integers.

Though Oracle provides an internal function to generate random numbers, there is no function that gives a random number between any two given integers. Sometimes this is needed to pull random data for test cases. The following function generates a random number between any given two integers. The script is easily updatable to include pulling random_date between two given dates (by converting the date into a Julian date and converting back to date). It has been tested on v. 8.1.7.3.

create or replace function myrand(a IN int,b IN int) 
return number
is
begin
return trunc(abs(( GREATEST(a,b) - LEAST(a,b) + 1 ) * abs(dbms_random.value)) + LEAST(a,b));
end;
/

Here is an example:

1* select myrand(10,20) from dual
SQL> /

MYRAND(10,20)
-------------
18

SQL> select myrand(-1,-20) from dual
2 
SQL> /

MYRAND(-1,-20)
--------------
-16

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close