Search the Site

Sponsors

bottom corner

Creating an Oracle table pre-filled with random values

To test out a newly written database application, we often need a large quantity of test data in order to see if the program handles various things. This sample demonstrates how we can quickly generate an Oracle database table pre-filled with as much test data as we need.

This page is filed under keyword(s): oracle.

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
);
begin
  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;
  commit;
end;

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;

  COUNT(*)
----------
     10000

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

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 17 Dec 2008

bottom corner