How to Generate Random Data (Numbers, Strings and Dates) in Oracle? Options

codeling Posts: 1057 Points: 4443
Posted: Monday, January 11, 2016 11:25:51 AM

VALUE Functions

The basic function gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). Alternatively, you can get a random Oracle number x, where x is greater than or equal to low and less than high.

DBMS_RANDOM.VALUE
  RETURN NUMBER;

DBMS_RANDOM.VALUE(
  low  IN  NUMBER,
  high IN  NUMBER)
RETURN NUMBER;

The parameter low specifies the lowest number in a range from which to generate a random number. The number generated may be equal to low
The parameter high specifies the highest number below which to generate a random number. The number generated will be less than high

Examples

1. Generating a random number between 0 and 1

select dbms_random.value from dual;

0.43479573301642652177742938323830039513

2. Generating a random number from a range, between 1 to 1000

select dbms_random.value(1,1000) from dual;

330.387916712441729077598383255477731219

3. Generating a 14 digit random number

select dbms_random.value(10000000000000, 99999999999999) from dual;

89926505345163.88395060783333581957965101


codeling Posts: 1057 Points: 4443
Posted: Wednesday, January 13, 2016 3:20:44 PM

Generating Random Dates

There are no specific functions in the DBMS_RANDOM package for generating random dates, but we can easliy generate random dates by adding random numbers to an existing date to make it random.

Examples

1. Generating random dates over the next year

select TRUNC(SYSDATE + DBMS_RANDOM.value(0,366))) from dual;

2. Adding random hours, minutes and seconds to a date

select TRUNC(SYSDATE) + TRUNC(DBMS_RANDOM.value(0,1000))/24 from dual;

select TRUNC(SYSDATE) + TRUNC(DBMS_RANDOM.value(0,1000))/ (24*60) from dual;

select TRUNC(SYSDATE) + TRUNC(DBMS_RANDOM.value(0,1000))/(24*60*60) from dual;

Users browsing this topic
Guest