OraMatt: YABAOracle

Sometimes I need to create random datasets to explore how queries will run, so I whipped up this little sample procedure as a way to do it.

This is basically just in case I forget… enjoy

Create a quick test table

create table test1 
		(id NUMBER GENERATED BY DEFAULT AS IDENTITY
 			(START WITH 100 INCREMENT BY 10) /*new 12c identify datatype */,
 		name varchar2(50),
 		salary number,
 		commission number,
 		indate timestamp)
 /

Insert some base data

insert into test (name,salary,commission,indate) values ('matt',100,10,systimestamp);
insert into test (name,salary,commission,indate) values ('greg',500,25,systimestamp);
commit;

Procedure for creating, collecting & inserting random data

- Create procedure to insert data
create or replace procedure insert_rando_data (v_howmany in number)
	AS
	cursor b_howmany is select count(*) from matt.test1;
	cursor a_howmany is select count(*) from matt.test1;
	
	b_count number(10);
	a_count number(10);

	type t_test IS TABLE OF test1%ROWTYPE;

 	l_howmany NUMBER; 

 	l_start	NUMBER;

 	l_test t_test := t_test();

BEGIN 
	for i in 1..v_howmany LOOP

	l_test.extend;

	l_test(l_test.last).id 			:= dbms_random.value(10, 9999999);
	l_test(l_test.last).name		:= dbms_random.string( 'a', TRUNC( dbms_random.value( 5, 30 ) ) );
	l_test(l_test.last).salary		:= dbms_random.value(1000, 99999);
	l_test(l_test.last).commission	:= dbms_random.value(100, 999);
	l_test(l_test.last).indate		:= systimestamp;

END LOOP;

-- Show number before insert

open a_howmany;
fetch a_howmany into a_count;
dbms_output.put_line('You currently have '||a_count|| ' rows of data');
close a_howmany;

-- time the insert
l_start := DBMS_UTILITY.get_time;

FORALL x IN l_test.first .. l_test.last
	INSERT INTO test1 VALUES l_test(x);
COMMIT;

-- Show number after insert


open b_howmany;
fetch b_howmany into b_count;
dbms_output.put_line('You now have '||b_count|| ' rows of data');
close b_howmany;

dbms_output.put_line('Insert took  : ' || (DBMS_UTILITY.get_time - l_start) || ' seconds!');


END;
/

Run the procedure by calling like so…

exec insert_rando_data(1000000);
You currently have 0 rows of data
You now have 1000000 rows of data
Insert took  : 859 seconds!

PL/SQL procedure successfully completed.