Using bind variables

We have discussed bind variables in the A working example recipe in Chapter 1, Starting with Performance Tuning.

In this recipe, it is time to dig deeper into this topic, illustrating the benefits of using bind variables and testing the result of our efforts with simple examples. We will see examples on query statements, but the same methodologies and results apply to DML statements.

Getting ready

Follow these steps to prepare the database:

1. Create a package named Chapter4 to test various aspects related to bind variables.

2. Connect to SQL*Plus using the SH schema:

   CONNECT sh@TESTDB/sh
   

3. Create the required package:

   CREATE OR REPLACE PACKAGE sh.CHAPTER4 AS
     PROCEDURE WORKLOAD_NOBIND;
     PROCEDURE WORKLOAD_BIND;
     PROCEDURE WORKLOAD_BIND_STATIC;
     PROCEDURE TEST_INJECTION(NAME IN
       sh.customers.cust_last_name%TYPE);
     PROCEDURE TEST_INJECTION2(NAME IN
       sh.customers.cust_last_name%TYPE);
   END;
   /
   
   CREATE OR REPLACE PACKAGE BODY sh.CHAPTER4 AS
     PROCEDURE TEST_NOBIND(CUSTID IN...