# create the table(s), primary key, sequence, and trigger
# the sequence and trigger auto populate the primary key
CREATE TABLE dept (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE dept ADD (
CONSTRAINT dept_pk1 PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq START WITH 1;
Trigger definition:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
CREATE TABLE dept_2 (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE dept_2 ADD (
CONSTRAINT dept_pk2 PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq2 START WITH 1;
Trigger definition:
CREATE OR REPLACE TRIGGER dept_bir_2
BEFORE INSERT ON dept_2
FOR EACH ROW
BEGIN
SELECT dept_seq2.NEXTVAL
INTO :new.id
FROM dual;
END;
/
CREATE TABLE dept_3 (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);
ALTER TABLE dept_3 ADD (
CONSTRAINT dept_pk3 PRIMARY KEY (ID));
CREATE SEQUENCE dept_seq3 START WITH 1;
Trigger definition:
CREATE OR REPLACE TRIGGER dept_bir_3
BEFORE INSERT ON dept_3
FOR EACH ROW
BEGIN
SELECT dept_seq3.NEXTVAL
INTO :new.id
FROM dual;
END;
/
# create a package that inserts in very simple “dept_X” tables and inserts X number of rows of 20 character strings
CREATE OR REPLACE PACKAGE myLoad AS
PROCEDURE insertIntoDept (p_rowsInsert NUMBER);
PROCEDURE insertIntoDept_2 (p_rowsInsert NUMBER);
PROCEDURE insertIntoDept_3(p_rowsInsert NUMBER);
END myLoad;
/
CREATE OR REPLACE PACKAGE BODY myLoad AS
PROCEDURE insertIntoDept(p_rowsInsert IN NUMBER) AS
v_idx number:=0;
BEGIN
FOR i IN 1..p_rowsInsert LOOP
insert into dept (description) select dbms_random.string(‘U’, 20) str from dual;
END LOOP;
END insertIntoDept;
PROCEDURE insertIntoDept_2(p_rowsInsert IN NUMBER) AS
v_idx number:=0;
BEGIN
FOR i IN 1..p_rowsInsert LOOP
insert into dept_2 (description) select dbms_random.string(‘U’, 20) str from dual;
END LOOP;
END insertIntoDept_2;
PROCEDURE insertIntoDept_3(p_rowsInsert IN NUMBER) AS
v_idx number:=0;
BEGIN
FOR i IN 1..p_rowsInsert LOOP
insert into dept_3(description) select dbms_random.string(‘U’, 20) str from dual;
END LOOP;
END insertIntoDept_3;
END myLoad;
/
# now call the loader
execute myload.insertIntoDept(12);
execute myload.insertIntoDept_2(38);
execute myload.insertIntoDept_3(38);
commit;