set serveroutput on;

-- The script show difference in terms of performance between INSERT statements 
-- with bind variables and without them.
-- The sequence of actions is the following:
-- 1. 10000 rows is inserted into the PRODUCT table with statements not using bind variables
-- 2. Inserted rows are removed from the PRODUCT table
-- 3. 10000 rows is inserted into the PRODUCT table with statements using bind variables
-- 4. Inserted rows are removed from the PRODUCT table
-- 5. Steps 2-5 are repeated but this time procedure using bind variables is called first.


PROMPT Inserting 10000 rows into the PRODUCT table without using bind variables...
exec no_bv_inserts

PROMPT Clean up of the PRODUCT table...
DELETE FROM PRODUCT WHERE PRODID > 300000;
COMMIT;

PROMPT Inserting 10000 rows into the PRODUCT table using bind variables...
exec bv_inserts

PROMPT Clean up of the PRODUCT table...
DELETE FROM PRODUCT WHERE PRODID > 300000;
COMMIT;

PROMPT 
PROMPT ----------------------------------------------------------------------------

PROMPT 
PROMPT Now the order in which we are using and not using bind variables will be reversed.
PROMPT 

PROMPT Inserting 10000 rows into the PRODUCT table using bind variables...
exec bv_inserts

PROMPT Clean up of the PRODUCT table...
DELETE FROM PRODUCT WHERE PRODID > 300000;
COMMIT;

PROMPT Inserting 10000 rows into the PRODUCT table without using bind variables...
exec no_bv_inserts

PROMPT Clean up of the PRODUCT table...
DELETE FROM PRODUCT WHERE PRODID > 300000;
COMMIT;
