Create sample table and insert random data in database

if you are trying to do some testing and want to insert some random data into tables.

Lets say table name is T1.

CREATE TABLE T1
(  ID NUMBER ,
NAME VARCHAR2(20 BYTE) )
TABLESPACE USERS;

Insert Data :
Here is a simple plsql block to insert 1500 rows into a table with unique ID and random string of 20
characters.

DECLARE
  i NUMBER :=1;
BEGIN
  LOOP
    i := i+1;
    insert into T1 values(i,(SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL));
    commit;
    dbms_output.put_line (i);
    EXIT
  WHEN i >= 1500;
  END LOOP;
END;

select count (*) from T1;
-- result : 1500 rows

-- Note : You can alter the insert statement in pl/sql block as you want.
-- You can increase the data by increasing i value to what ever you desire. Example i >= 15000;
-- You can also increase the sequence by altering i := i +2 etc..

STRING

The STRING function returns a string of random characters of the specified length. The OPT parameter determines the type of string produced as follows:

• 'u', 'U' - uppercase alpha characters
• 'l', 'L' - lowercase alpha characters
• 'a', 'A' - mixed case alpha characters
• 'x', 'X' - uppercase alpha-numeric characters
• 'p', 'P' - any printable characters

To include sysdate :

There are no specific functions for generating random dates, but we can add random numbers to an existing date to make it random. The following example generates random dates over the next year.

DECLARE
  i NUMBER :=10;
BEGIN
  LOOP
    i := i+10;
    insert into T1 values(i,(SELECT DBMS_RANDOM.STRING('A', 10) FROM DUAL),(SELECT TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) from dual));
    commit;
    dbms_output.put_line (i);
    EXIT
  WHEN i >= 150;
  END LOOP;
END;

-- Note : By doing the correct divisions, we can add random numbers of hours, seconds or minutes to a date. We can do that by simple sql :

(TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day))