Use DBMS_RANDOM to generate text number date and time stamp values

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

SELECT DBMS_RANDOM.VALUE FROM DUAL;

For integers in a given range, add the arguments low_value and high_value, andtruncate the decimals from the result (the high value is not included as a possible value). So, for integers from 0 to 99,you would use this code:

SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;

To generate random text strings, use the STRING function andsupply a code indicating the type of string and the desired length:

SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;

The type codes are documented in the Oracle Database 10g PL/SQL Packages and Types Reference. Here are a few type codes: 'U' generates uppercase, 'L' generateslowercase, and 'A' generates mixed case.
Oracle stores dates as integer offsets from a key date inthe past (January 1, 4712 B.C., in case you were curious). This means that youcan generate random dates in a given range by finding the integer thatcorresponds to your desired start date, and then adding a random integer to it.

You can generate the internal date number for today's dateby using the TO_CHAR function with the 'J' format code:

SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

For example, to generate random dates during the year 2003,you would first determine the date integer for January 1, 2003:

SELECT TO_CHAR(TO_DATE('01/01/03','mm/dd/yy'),'J')

FROM DUAL;

The system responds with 2452641. So, to generate a randomdate within the year, we use DBMS_RANDOM.VALUE with a low_valueof 2452641 and a high_value of 2452641+364, andconvert it to a date:

SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')
FROM DUAL;

This is how to generate Random Time Stamp between two dates:

alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';

SELECT   TO_DATE ('01/07/2011', 'DD/MM/YYYY  HH24:MI:SS')
       +   DBMS_RANDOM.VALUE (0,
                                  86400
                                * (  TO_DATE ('30-07-2011', 'DD/MM/YYYY  HH24:MI:SS')
                                   - TO_DATE ('02-07-2011', 'DD/MM/YYYY  HH24:MI:SS')
                                  )
                              + 1
                             )
         / 86400
  FROM DUAL;

Get Oracle Certifications for all Exams
Free Online Exams.com