René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

A dummy data generator for Oracle

create table foo_1 (v varchar2(15));
create table foo_2 (v varchar2(15));
create table foo_3 (v varchar2(15));
create table foo_4 (v varchar2(15));
create table foo_5 (v varchar2(15));
create table foo_6 (v varchar2(15));

insert into foo_1 values ('lorem');
insert into foo_1 values ('ipsum');
insert into foo_1 values ('dolor');
insert into foo_1 values ('sit');
insert into foo_1 values ('amet');
insert into foo_1 values ('consectetuer');
insert into foo_1 values ('adipiscing');
insert into foo_1 values ('elit');
insert into foo_1 values ('sed');
insert into foo_1 values ('diam');

insert into foo_2 values ('nonummy');
insert into foo_2 values ('nibh');
insert into foo_2 values ('euismod');
insert into foo_2 values ('tincidunt');
insert into foo_2 values ('ut');
insert into foo_2 values ('laoreet');
insert into foo_2 values ('dolore');
insert into foo_2 values ('magna');
insert into foo_2 values ('aliquip');
insert into foo_2 values ('erat');

insert into foo_3 values ('volutpat');
insert into foo_3 values ('wisi');
insert into foo_3 values ('enim');
insert into foo_3 values ('ad');
insert into foo_3 values ('minim');
insert into foo_3 values ('veniam');
insert into foo_3 values ('quis');
insert into foo_3 values ('nostrud');
insert into foo_3 values ('tation');
insert into foo_3 values ('ullamcorper');

insert into foo_4 values ('suscipit');
insert into foo_4 values ('feugiat');
insert into foo_4 values ('nulla');
insert into foo_4 values ('facilisis');
insert into foo_4 values ('vero');
insert into foo_4 values ('consequat');
insert into foo_4 values ('autem');
insert into foo_4 values ('vel');
insert into foo_4 values ('eum');
insert into foo_4 values ('iriure');

insert into foo_5 values ('hendrerit');
insert into foo_5 values ('lobortis');
insert into foo_5 values ('odio');
insert into foo_5 values ('vulputate');
insert into foo_5 values ('aliquam');
insert into foo_5 values ('esse');
insert into foo_5 values ('commodo');
insert into foo_5 values ('illum');
insert into foo_5 values ('praesent');
insert into foo_5 values ('ex');

insert into foo_6 values ('dignissim');
insert into foo_6 values ('placerat');
insert into foo_6 values ('possim');
insert into foo_6 values ('facer');
insert into foo_6 values ('veniam');
insert into foo_6 values ('wisi');
insert into foo_6 values ('ullamcorper');
insert into foo_6 values ('molestie');
insert into foo_6 values ('excerci');
insert into foo_6 values ('doming');

create table test_for_tk_1 (
  id      number(8),
  nm      varchar2(100),
  ob      varchar2(20)
) storage (pctincrease 0 initial 100M next 100M);

create sequence s start with 1 increment by 1;

set transaction use rollback segment rb_big;

declare
  cursor c1 is select v from foo_1;
  cursor c2 is select v from foo_2;
  cursor c3 is select v from foo_3;
  cursor c4 is select v from foo_4;
  cursor c5 is select v from foo_5;
  cursor c6 is select v from foo_6;
  rnd      number;
  v_ob     varchar2(20);
begin

  dbms_random.initialize(1094093);
  for r1 in c1 loop
  for r2 in c2 loop
  for r3 in c3 loop
  for r4 in c4 loop
  for r5 in c5 loop
  for r6 in c6 loop

  rnd := dbms_random.value;

  if rnd < 1/3 then 
    v_ob:='occurs 0.33333';
  elsif rnd < 1/3+1/4 then
    v_ob:='a fourth';
  elsif rnd < 1/3+1/4+1/5 then
    v_ob:='here comes the sun';
  elsif rnd < 1/3+1/4+1/5+1/6 then
    v_ob:='hungry';
  else
    v_ob:=dbms_random.string('l',15);
  end if;
    
    insert into test_for_tk_1 values 
        ( s.nextval,
            r1.v || ' ' ||
            r2.v || ' ' ||
            r3.v || ' ' ||
            r4.v || ' ' ||
            r5.v || ' ' ||
            r6.v,
            v_ob);
  end loop;
  end loop;
  end loop;
  end loop;
  end loop;
  end loop;
end;
/

select blocks from dba_segments where segment_name = 'TEST_FOR_TK_1';