To demonstrate a real-life example, you will create a simple test harness that will generate a lot of INSERT statements and insert into a table called TRANS.
create table trans (
trans_id number,
cust_name varchar2(20),
trans_dt date,
trans_amt number(8,2),
store_id number(2)
)/
Here is the little PL/SQL code snippet that does the trick. It generates 1,000 insert statements and executes them. (Note that it generates 1,000 distinct insert statements, not inserts 1,000 times in the same statement or program.)
declare
l_stmt varchar2(2000);
begin
for ctr in 1..1000 loop
l_stmt := ‘insert into trans values (‘||
trans_id_seq.nextval||’,’||
‘’’’||dbms_random.string(‘U’,20)||’’’,’||
‘sysdate - ‘||
round(dbms_random.value(1,365))||’,’||
round(dbms_random.value(1,99999999),2)||’,’||
round(dbms_random.value(1,99))||’)’;
dbms_output.put_line(l_stmt);
execute immediate l_stmt;
commit;
end loop;
end;
Just create the file with the contents as above; do not run it. Call this file add_trans.sql.