Create a Data Generator using sequence and a loop in Oracle DB

Mar 1, 2013 | Oracle, oracle pl/sql, Scripts, Scripts & Batch

Create a sequence (i.e. “tmp” starting with 10001) to be used in statements:

[sql]CREATE SEQUENCE TMP
START WITH 10001
MAXVALUE 9999999999999999999999
MINVALUE 1[/sql]

Enter the following in a DB-Tool editor:

[sql]begin
FOR Lcntr IN 1..10000
LOOP
insert into customer (surname, forname, some_id, some_field)
values (‘SN’ || tmp.nextval, ‘FN’ || tmp.nextval, 123456, ‘abcdef’);
END LOOP;
commit;
end;[/sql]

Output should be something like this:

[text]SN50076,FN50076,123456,abcdef
SN50077,FN50077,123456,abcdef
SN50078,FN50078,123456,abcdef
SN50079,FN50079,123456,abcdef
SN50080,FN50080,123456,abcdef
SN50081,FN50081,123456,abcdef[/text]