Creating an Oracle table pre-filled with random values

The code makes use of Oracle’s dbms_random package and some very light-weight PL/SQL. You may adjust the number of fields, field data types, total number of records, etc. to suit your needs.

create table schema.test_data (
  txt  varchar2(10), 
  numb  number(10), 
  dt  date
  for i in 1..10000 loop
    insert into schema.test_data values (dbms_random.string('X',10), dbms_random.value(0,20), to_date(trunc(dbms_random.value(2422599, 2457085)),'j'));
  end loop;

When we are done, we will see that the table contains 10,000 records as we specified in the sample code above.

select count(*) from schema.test_data;


Below is what the data may look like.

select txt, numb, to_char(dt,'mm/dd/yyyy') as dt from schema.test_data where rownum<10;

TXT              NUMB DT
---------- ---------- ----------
35W6DQ986O          7 11/26/1943
8NOOSRH6R2          8 01/18/1993
7HPKA10GKQ         16 10/07/2012
I90Z9YVWHW          2 11/01/1939
WNNW1M7BNM         15 10/27/1982
R9OQF67QWP         13 08/28/1926
PD39YGY35D         10 02/04/1952
N8R8DKMAIO         15 07/06/1986
43MELQ9M0Q          3 06/19/1921

Leave a Reply

Your email address will not be published. Required fields are marked *